Access to Excel Using CurrentRegion

Trevor G

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

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,193
Office Version
365
Platform
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,664
Office Version
2016
Platform
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
54,193
Office Version
365
Platform
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,664
Office Version
2016
Platform
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,664
Office Version
2016
Platform
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,664
Office Version
2016
Platform
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

?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,813
Messages
5,513,538
Members
408,957
Latest member
Jcoverick

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top