Query results into existing Excel worksheet 2

[JSchumacher]

I spent quite a bit of time yesterday finding a solution to a problem, and I did get it to work, but I feel sure there is a much easier way to accomplish it. I welcome any ideas on making this work better.

I have a Union query that returns 48 rows (theoretically this number is consistent, realistically...?). I want to place those rows in an existing Excel spreadsheet. To do this I outputted the results to a new spreadsheet and then copied and pasted to the existing spreadsheet.

Here is the code I came up with:

Private Sub RunVolumeReport()

    Dim objXL As Excel.Application
    Dim xlWBMain As Excel.Workbook
    Dim xlWSMain As Excel.Worksheet
    Dim xlWBNew As Excel.Workbook
    Dim xlWSNew As Excel.Worksheet

    Set objXL = CreateObject("Excel.Application")
    Set xlWBMain = objXL.Workbooks.Open("s:\import files\volume.xls")
    Set xlWSMain = xlWBMain.Worksheets(2)
    objXL.Visible = True
    xlWSMain.Activate

    DoCmd.OutputTo acOutputQuery, "qryVolume", acFormatXLS, "s:\import files\NewVolume.xls"
    Set xlWBNew = objXL.Workbooks.Open("s:\import files\NewVolume.xls")
    Set xlWSNew = xlWBNew.Worksheets(1)
    xlWSNew.Range("a2:d49").Copy
    xlWSMain.Range("a2:d49").PasteSpecial
    xlWSNew.Range("a2:d49").ClearOutline
    
    'Close Excel
    xlWBMain.Close True 'save changes to Main page
    xlWBNew.Close False
    objXL.Quit
    
    'Release objects from memory
    Set xlWSMain = Nothing
    Set xlWBMain = Nothing
    Set xlWSNew = Nothing
    Set xlWBNew = Nothing
    Set objXL = Nothing
End Sub

Thanks for any ideas!

Judie

 

[padinka]

Here's some code. Run it in access, not excel. Th (DoCmd.TransferSpreadsheet acExport, 8,) code will APPEND your workbook unless the exact query name already exists and then it will overwrite. I have written something to copy your query to a unique name when this is runs(see the format ()) so that it will not overwrite unless it runs more than once on the same day. One note: the query name can not be longer than 24 characters.

(By the way, I haven't debugged this exact code, I just wrote if for you. And I made it very basic.)

Sub Output_To_Existing_Workbook()

Dim mySpreadsheetName
Dim myFileName
Dim myQueryName

'Replace the "" with the full path of your file e.g. "C:\temp\test.xls"
myFileName = ""

'Replace the "" with the query name e.g. "qryOutput"
myQueryName = ""

'Replace the "" with the name you want for the excel tab e.g. "Output Data"
'to make this automated, I have appended the date to the end as each
'tab in excel must have a unique name or this proc will overwrite existing data


mySpreadsheetName = "" & " " &Format(Now(), "mmddyy&quot


DoCmd.CopyObject "", myQueryName, acQuery, mySpreadsheetName
DoCmd.TransferSpreadsheet acExport, 8, myQueryName, myFileName, True, ""
DoCmd.DeleteObject acQuery, mySpreadsheetName

End Sub

Trisha
padinka@yahoo.com

 

[educate889]

JSchumacher,

I tried a piece of your above code and am getting a bunch of errors.

Will your code (or modifications you suggest) allow me to output multiple queries to individual worksheets within a single workbook.

I have XCM101, XCM102, XCM103 and need them to export to a single excel file(with mulitple tabs/worksheets) I called h:\XCM_RAWDATA

Your help or others would be greatly appreciated!

 

[padinka]

That's really easy. I thought you wanted to add to existing spreadsheet with the same query over and over. Here's your code:

Use Kill to get rid of your old file otherwise this code will add to it so make a copy of anything you want to keep before you run this.

DoCmd.TransferSpreadsheet acExport, 8, "XCM101", "h:\XCM_RAWDATA.xls", True, ""

DoCmd.TransferSpreadsheet acExport, 8, "XCM102", "h:\XCM_RAWDATA.xls", True, ""

DoCmd.TransferSpreadsheet acExport, 8, "XCM103", "h:\XCM_RAWDATA.xls", True, ""

Using excel acExport 8, instead of overwriting, it adds a sheet.
Trisha
padinka@yahoo.com