Run a Macro in a *.xls from VB

[finny]

Hi all,
I have a VB dll that opens an existing spreadsheet, populates with data, renames and closes both the old and new. The problem I am running into is this...
The data I input into the spreadsheet is raw. In the spreadsheet itself I have a macro that formats the raw data into a user-friendly "report". I'm having problems kicking off the macro from my dll. The only code that even seems to recognize that I'm call a macro is

app.ExecuteExcel4Macro("macroname&quot

And that doesn't run correctly. I get the busy application(switch to/retry) dialog box.

Another syntax I thought would work work doesn't even recognize I'm calling a macro.

app.run("macroname&quot

Any help on this would be appreciated.

Thx in advance...finny

 

[finny]

Here is the code I'm using in case anyone was looking for it. Also, I'm relatively new to this so any comments on coding techniques are always welcome.

Thx again...finny


Public Function CreateDeviation(sql As Variant, sUser As Variant) As String
Dim sTempDir, sTempFile, sStartFile As String
Dim objExcel As New excel.Application
Dim objBook, objArticle As Object
Dim iFldCt, iRecCt As Integer
Dim i, iColNum, iRowNum As Integer
Dim fso, txtfile

sTempDir = "d:\projects\forecast\forecast_Local\"
sTempFile = "Deviation.xls"
sStartFile = sTempDir & sTempFile
On Error Resume Next

''get data

Set OraDynaset = OraDatabase.CreateDynaset(sql, 0)
i = OraDynaset.RecordCount
iFldCt = OraDynaset.Fields.Count

'set excel workbook and worksheet objects

Set objBook = objExcel.Workbooks.Open(sStartFile)
'
With objBook
Set objArticle = .Sheets("DataSheet&quot
End With

'''Place column titles
With objArticle
For i = 0 To iFldCt - 1
'code to place column headers
Next i
End With

''Place values into cells
Do Until OraDynaset.EOF
'code to file in worksheet
Loop

Dim sNewFile As String
Dim sFullFile As String
Dim sMacro As String

sNewFile = sUser & "_Deviation.xls"
sFullFile = sTempDir & sNewFile

""******THIS IS WHERE I WOULD LIKE TO RUN THE MACRO

sMacro = sNewFile & "!formatdeviation"
objExcel.ExecuteExcel4Macro (sMacro)


On Error Resume Next

objBook.SaveAs (sFullFile), , , , , , , xlOtherSessionChanges
objBook.Close (sFullFile)
objBook.Close (sStartFile)
Set objExcel = Nothing
CreateDeviation = sFullFile

End Function

 

[finny]

got it. make sure you are calling the application object and not the workbook object when you use the run method.

another problem though. it works when testing the dll with a form in the project, but it hangs up when calling the dll from an asp page. any ideas?

thx.