convert Excel VBA to run in Access.

Curry

Registered User.
Local time
Tomorrow, 00:30
Joined
Jul 21, 2003
Messages
73
Does anybody know how to amend this code so it works in Access.


Set xlApp = CreateObject("Excel.Application")

xlApp.Cells.Select
xlApp.Selection.Subtotal GroupBy:=11, Function:=xlSum, TotalList:=Array(10), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
xlApp.ActiveSheet.Outline.ShowLevels RowLevels:=2
xlApp.Range("M1").Select

It currently gets stuck on the line

xlApp.Selection.Subtotal GroupBy:=11, Function:=xlSum, TotalList:=Array(10), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Thanks
 
I assme you are opening Excel and then wanting that code to run for Excel.

If so, I find the best was (also for Word) is to run the Excel or Word macro from Access.

For example I have the following as some code run from Access event.

Dim objXLApp As Object
Dim objXLBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\Letters\ExtractEmailDetails.xls")
Set oSheet = objXLBook.Worksheets(1)
'objXLApp.Visible = True

objXLApp.Run "Macro1"
 
Yes that is what I am doing. I have some other formating code around this example that is working fine. Its just this one line I cannot get to run.
THanks
 
It "gets stuck?"
There's gotta be more you can say about the problem! ;) An error description or something.
 
"Run Time Error 1004
Subtotal method of range class failed"

The rest of the formating code before this line runs correctly. If I remove this line I do not get an error and the Excel worksheet formats accordingly. I just cannot get this line to run.
 
The clue is in this part of the statement:

xlApp.Selection.Subtotal GroupBy:=11, Function:=xlSum, TotalList:=Array(10), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Because you are using late-binding to create and manipulate the Excel.Application object, you do not have a VB Reference set for the Microsoft Excel Object Library.

So, you will need to add the following line to the beginning of your code:
Code:
[COLOR=navy]Const[/COLOR] xlSum = -4157
 

Users who are viewing this thread

Back
Top Bottom