Access VBA to manipulate an Excel spreadsheet

  • Thread starter Thread starter Shell
  • Start date Start date
S

Shell

I have written some code in Excel 2000. COde shown below.

Private Sub Worksheet_Activate()
Dim pvt As PivotTable
Dim i As Long
Dim d As Variant
Dim rge As Range

If gblnSheet3Executed = True Then
Exit Sub
End If

'Get max row
i = 1
d = 1
Do Until d = ""
i = i + 1
d = Sheet4.Range("A" & i).Value
Loop


'Refresh the pivot table
For Each pvt In Me.PivotTables
pvt.SourceData = Sheets("AbbreviatedRawData").Range("A1:H" & i -
1).CurrentRegion.Address(True, True, xlR1C1, True)
pvt.RefreshTable
gblnSheet3Executed = True
Next pvt

End Sub


I need to run this code in in Access 2000 instead. I know I won't need the
first if statement. But I don't know how to implement this code in Access.

Can someone direct me?

Thanks
 
The first thing that you'll need to do is add a reference to Excel. In
the IDE (Alt-F11), Tools->References. Find the Microsoft Excel
version, and check it.

Now, it'll look something like this:
Sub ExcelMacro()

Dim xls As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim pvt As Excel.PivotTable

Set xls = New Excel.Application
Set wkb = xls.Workbooks.Open("C:\filename.xls")
Set wks = wkb.Worksheets("Sheet4")

Dim i As Integer

Do Until wks.Cells(i, 1) = ""
i = i + 1
Loop

'Now, refresh the pivot table
For Each pvt In wkb.Worksheets("NameOfWorksheet").PivotTables
pvt.SourceData = wkb.Worksheets("AbbreviatedRawData").Range
("A1:H" & i - 1).CurrentRegion.Address(True, True, xlR1C1, True)
pvt.RefreshTable

Next
wkb.Save
wkb.Close
xls.Quit
Set pvt = Nothing
Set wks = Nothing
Set wkb = Nothing
Set xls = Nothing
End Sub
 
The code works great until I get to the Refresh pivot table section.
I get a "Subscript out of range" error. Err Nbr = 9

On the line

For Each pvt In wkb.Worksheets("NameOfWorksheet").PivotTables

I am using the name of the tab whicj contains the pivot table.

Any suggestions

Thanks

Shell
 
Back
Top