use Access to open an existing Excel workbook 2

[ruthcali]

Hi,
How can i use Access to:

-open an existing excel workbook,
-create a new worksheet in excel and name it the current date
-put all the results of an access query into that new worksheet starting in cell A2
-leave excel open so the user can see the data.

Thanks!

 

[SBendBuckeye]

Hello RuthCali,

It would be easier to export the query and then copy it into your workbook.

Step 1 - Export the query to an Excel workbook file

Look up DoCmd.TransferSpreadsheet. The souce for an export can be either a table or a query.

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel97, YourQueryName, YourExcelName

Step 2 - Link Excel object dll into Access

Set a reference using Tools, References when in the VBA code editor to Microsoft Excel x.x Object Library. Your code will not work without this step.

Step 3 - Create an Excel object in Access and process it

Dim objXLApp As Excel.Application
Dim wkb As WorkBook
Dim wks As WorkSheet
Dim strSaveFile As String
Dim strExportFile As String

Set objXLApp = CreateObject("Excel.Application&quot

'Open the workbook you exported from Access via a Query
objXLApp.Workbooks.Open(YourExportFileNameWithPath)
strExportFile = objXLApp.Workbooks(1).Name

'Open the workbook into which you want to combine data
objXLApp.Workbooks.Open(YourOldFileNameWithPath)
strSaveFile = objXLApp.Workbooks(2).Name

'Set a reference to this workbook to make things easier
Set wkb = objXLApp.Workbooks(strSaveFile)

'Add an additional worksheet to hold copied data
wkb.Sheets.Add After:=wkb.Worksheets(wkb.Worksheets.Count)

'Set a reference to this worksheet to make things easier
set wks = wkb.Sheets(wkb.Worksheets(wkb.Worksheets.Count)

'Name it
wks.Name = Date()

'Select and copy exported data to the clipboard
objXLApp.Workbooks(strExportFile).Cells.Select
Selection.Copy

'Activate the other workbook and newly created spreadsheet
wkb.Activate
wks.Activate

'Copy the clipboard to A2 in the save spreadsheet file
wks.Range("A2&quot.Select
wks.Paste

'Resize the spreadsheet for your data
wks.Cells.Select
wks.Cells.EntireColumn.AutoFit

wks.Range("A2&quot.Activate

'Now make it visible to your user
objXLApp.Visible = True

Since you created it from Access, you must close it from Access or react to an automation error caused by the user closing the WorkBook or Excel itself.

Good Luck!

 

[ruthcali]

Wow, thanks! that's amazing.

Excel doesn't allow slashes in the tab names, so i just had to change
wks.Name = Date() to
wks.Name = Format(Date, "medium date&quot

and it gave me a syntax error with:
set wks = wkb.Sheets(wkb.Worksheets(wkb.Worksheets.Count)
so i changed it to:
Set wks = objXLApp.Worksheets("sheet1&quot

Thank you so much for your help!!

 

[SBendBuckeye]

Sorry for the error. As usual, I forgot the last right paren ). Have a great day and thanks for the star!