Open Excel file, Run Macro, Save File, Close File

[Kendall66]

Does anyone have sample code that would help me in accomplishing these commands.

Open Excel file.
Run Macro in Excel file.
Save Excel File.
Close Excel file.


The file is on local drive. The Macro is a simple print command.


Thankyou in advance.

 

[JFoushee]

With VB, go to Project, References. Look for 'Microsoft Excel blah blah blah'. Select that. Now you have access to basically running your own version of Excel in the background.

Now you have access to these commands

Private Sub Form_Load()
  Dim ExcelApp As New Excel
  Set ExcelApp = New Excel
  
  Excel.Workbooks.Open "filename"
  Excel.Application.Run "macro"
  Excel.Application.SaveWorkspace
  Excel.Workbooks.Close
  
  Set Excel = Nothing
  
End Sub

I have no idea what works and doesn't. I would be appreciative if you revisit the site to mention what could be corrected. But I hope this is a good start.

 

[wasname]

Hi there... I'm trying to do the same thing and I'm very inexperienced with VB.

I've created a new project and I've added a reference to Excel and Word.

I've added the sample code listed above but get the following error on compile:

"Compile Error: Expected user-defined type, not project"
and highlights "Dim ExcelApp As New Excel"

Can someone tell me what I need to add/edit?

 

[JustinEzequiel]

Dim ExcelApp As New Excel.Application