Access to Excel Using CurrentRegion

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,708
Office Version
  1. 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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,982
Office Version
  1. 365
Platform
  1. Windows
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.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,708
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,982
Office Version
  1. 365
Platform
  1. Windows
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.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,708
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Hi, Trevor

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

F
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,708
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,708
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
what about

debug.print wssheet.range("A1").currentregion.address

?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,108
Messages
5,857,431
Members
431,879
Latest member
KiwDaWabbit

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
Top