Access to Excel Using CurrentRegion

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,717
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Trevor,

In situations like these, I have created Excel macros to format my data after it is exported. Then, just call that Excel macro from Access VBA. I found that easier than trying to run a bunch of different Excel commands directly from Access VBA.
 
Upvote 0
Thanks Joe,

I am generating new workbooks on the fly as it opens Excel to do the exporting so not so sure if I can run an Excel MACRO if the code only exists in Access, or am I wrong?
 
Upvote 0
Is there a methodology to the naming of the file?
If so, you may be able to program the same logic in the Excel macro so that it can find that file and perform the macro.

Likewise, if the file is the only file in its folder (maybe use a Temp folder), then the macro should be able to locate it.
 
Upvote 0
Yes there is logic for the file name and folder but sadly I am not the one who will run this function, it wll be run by various people in different locations around the UK.
 
Upvote 0
Hi, Trevor

Can you post a little bit of code to show how you are referring to the Excel file?

F
 
Upvote 0
Hi F,

Thanks for looking at the thread.

This is the extracted code which from the time of opening the excel workbook. The other code part is relevant to what happens in Access first.

Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application")
Dim wsSheet As Worksheet
xlapp.Application.ScreenUpdating = False

With xlapp
.Visible = True
.Workbooks.Open CurrentProject.Path & "\" & strFileName & ".xls"
For Each wsSheet In xlapp.Worksheets
wsSheet.Rows("1:5").Insert Shift:=xlDown
wsSheet.Range("b2").Value = "Casualty Team:"
wsSheet.Range("b3").Value = "Policies selected between:"
wsSheet.Range("b4").Value = "As at:"
wsSheet.Range("c2").Value = wsSheet.Range("A7").Value
wsSheet.Range("e4").Value = "Total Number of policies selected"
wsSheet.Range("f4").FormulaR1C1 = "=COUNT(C[-2])"
wsSheet.Range("c4") = Date
wsSheet.Columns("A:A").ClearContents
wsSheet.Columns("A:I").AutoFit
wsSheet.PageSetup.CenterHeader = wsSheet.Range("c2").Value
wsSheet.PageSetup.LeftFooter = "Printed: &D at &T"
wsSheet.PageSetup.Orientation = xlLandscape
wsSheet.PageSetup.Draft = False
wsSheet.PageSetup.PaperSize = xlPaperA4
wsSheet.PageSetup.Zoom = 100
wsSheet.PageSetup.PrintTitleRows = "$2:$6"
wsSheet.Range("b2:b4,e2:e4").Interior.ThemeColor = xlThemeColorDark1 '.TintAndShade = -0.149998474074526
wsSheet.Range("b2:b4,e2:e4").Interior.TintAndShade = -0.149998474074526 '
wsSheet.Range("d6").HorizontalAlignment = xlRight
wsSheet.Range("b2:f4").Borders.LineStyle = xlContinuous
wsSheet.Range("b2:f4").BorderAround xlDouble, xlThick
wsSheet.Columns("B:f").Font.Name = "Calibri"
Next wsSheet
xlapp.Application.ScreenUpdating = True
End With
Dim iCnt As Integer
Dim i As Integer
iCnt = xlapp.Application.Sheets.Count
For i = 1 To iCnt
xlapp.Application.Sheets(i).Name = "Peer Review " & i
Next
 
Upvote 0
I haven't tested, but I imagine .currentregion should work fine.

it is just a range.currentregion - I wonder if there is some confusion with worksheet.usedrange?

what if you add in a

debug.print wssheet.range("A1").currentregion.address
 
Upvote 0
I have just added this once again and it gives me an error selection method of range class failed - run time error 1004

wsSheet.Range("b6").CurrentRegion.Select

is it possible to ensure I can select the first then apply the boarder then select the next sheet and apply thte board, it may take a few extra seconds but I can live with that.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top