Export to Excel and Run Macro from .xls file

T

trusty1

Board Regular
Joined
Aug 8, 2005
Messages
50
Private Sub Command6_Click()

I had the below bit of code that worked fine in Access 97. But I cannot get the same Excel.Application to work in 2003.

I want the macro to export the query to excel. Then open excel and run a macros from the xls file.

Could you suggest how adapt this file to reflect 2003 coding.

Thanks


Code: 
Private Sub Command6_Click()

Dim xlapp As New Excel.Application

'Outputs file
    DoCmd.OutputTo acOutputQuery, "CashFlowDiffNonZero_DateFilter_old", acFormatXLS, "c:\Raw Data.xls", False

'starts excel
    Set xlapp = New Excel.Application
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Code: 
Private Sub Command6_Click()

Dim xlapp As New Excel.Application

'Outputs file
    DoCmd.OutputTo acOutputQuery, "CashFlowDiffNonZero_DateFilter_old", acFormatXLS, "c:\Raw Data.xls", False

'starts excel
    Set xlapp = New Excel.Application
Click to expand...

I believe your code is using something called "early binding". If this is the case, you will need to add a reference to the Excel Object Library in the Access VBA editor. Also, i think your final line of code should read:
Code: 
Set xlapp = CreateObject("Excel.Application")
If you wanted to further manipulate the excel file, i'd recommend assigning the excel workbook to an object variable (dim XLwkbk as Excel.WorkBook) and then setting that object = to the desired workbook (set XLwkbk = xlapp.WorkBooks("c:\Raw Data.xls")

Hope this helps,
Mike
 
Upvote 0
I use the following to open Excel from my access app:

Code: 
 Dim strPath         As String
 Dim strFile           As String
 Dim oApp            As Object
    
    strPath = "c:\temp"
    strFile = "whatever.xls"
        
    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True
    Set oApp = GetObject(strFile)
    oApp.Application.Visible = True
    oApp.Parent.Windows(1).Visible = True

hope it help
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Export To Excel .XLSX file
Replies
3
Views
268
Micron
M
K
  • Question
Opening a folder using windows explorer from a partitioned drive
Replies
3
Views
316
Kemidan2014
K
S
  • Question
Exporting 2 tables using 2 queries into one Excel file (2 different tabs)
Replies
0
Views
219
santa12345
S
R
  • Solved
Late Binding: extract Access to Excel with formatting
2 3
Replies
22
Views
3K
roelandwatteeuw
R
T
  • Solved
Problem suppressing the printing of Access report (from Excel)
Replies
7
Views
2K
Micron
M

Forum statistics

Threads
1,215,233
Messages
6,123,772
Members
449,123
Latest member
StorageQueen24

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top