Access to Excel Using CurrentRegion

[Trevor G]

How can I use the CurrentRegion command in Excel but from Access.

I am exporting multiple queries from Access to a single book and I have been asked to set a format to each sheet but they have different ranges so the use fo currentregion would be very useful.

 

[Trevor G]

Hi F

I have tried the debug.print and it stated I need a with statement.

I considered creating another module sheet to at least try and do some formatting on each sheet and ended up with a result I think I can work with. I will have to look to rethink this through over the weekend. I have a week or so to try and implement a number of reports into Excel where I have to format each sheet and also then do some other things, but for know if the below is working then I can move on.

I hope you have a lovely weekend.

Function baorder1()
Dim xlapp As Excel.Application
Dim wssheet As Worksheet
Set xlapp = CreateObject("Excel.Application")
With xlapp
.Visible = True
.Workbooks.Open CurrentProject.Path & "\" & "ExportResults QryExcel1.xls"
End With
Dim I As Long
For I = 1 To xlapp.Worksheets.Count
xlapp.Worksheets(I).Select
xlapp.Range("b7").Select
xlapp.Selection.CurrentRegion.Select
With xlapp.Selection

.Borders.LineStyle = xlContinuous
End With

Next I
xlapp.Worksheets(1).Select
End Function

 

[Fazza]

if you post the code where you use the debug.print I can understand what is happening

the debug.print error will be minor

if the debug.print works (or not), it demonstrates that you can use .currentregion (or not)

if you don't use the .select, it would help the original code. I think the errors are due to the selecting & that you are nearly there

when posting code, please use the code tags

though, it sounds like you are OK for now...?

 

[Trevor G]

Yes I should use the code tags, I sometimes just get swept away with trying to sort an issue.

I have now completed this task as I then used the xlApp at the end of my long amount of code to select the first sheet in the book and I am now formatting what I need to.

So happy bunny (well for today....)

 

[Fazza]

great
well done