VBA for Opening a Variably Named File in Excel and Copying it's Workbook Contents to Another Workbook

Couldn't you add to the code in Access to paste the contents of the newly created file to the template file?

The code in Access should have the new file's name so that wouldn't be a problem.

We need checks and balances before the values get to the Invoice Template.  

Also, to my knowledge, Access 97 will not write data to an existing Excel file, especially if the Excel file is formatted and not just a "raw" workbook.

I wasn't suggesting you change the existing code for creating the Excel file.

I'm suggesting you add code to it that copy/pastes from the new file to the template.

Assuming you specify the filename of the new file in the existing code you can use that in the copy/paste code.

I'm not sure I'm following.  Can you give me an example?

Can you post the code you are using to create the new Excel file?

It's just the do.cmd transferspreadsheet method on a query in the database.  I don't have access to the code right now. Later today, though.

This is a rough example that assumes you use a variable for the name of the Excel file being created.

Dim xlApp As Object
Dim xlWB As Object
Dim xlWBTemp As Object
Dim xlWS As Object
Dim strFileName As String

    ' specify the name of the Excel file being created
    strFileName = "C:\Query - " & Format(Date, "ddmmmyyyy") & ".xls"

    ' do the export
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TheExportQuery", strFileName

    ' now open the new file in Excel
    Set xlApp = CreateObject("Excel.Application")

    Set xlWB = Workbooks.Open(strFileName)

    ' open the Invoice template file
    Set xlWBTemp = Workbooks.Open("C:\Templates\Invoice Template.xls")

    ' copy/paste date between the 2 workbooks

    ' close workbooks and Excel

    xlWBTemp.Close SaveChanges:=True    ' perhaps use SaveAs to give the template a different name

    Set xlWBTemp = Nothing

    xlWB.Close SaveChanges:=False

    Set xlWB = Nothing

    xlApp.Quit

    Set xlApp = Nothing

Select allOpen in new window

     
I've left the copy/paste part empty because I don't know what you want to copy/paste.

Wow!  This looks great. A few questions:

What version is Excel9?  I believe Access 97 will only create a spreadsheet up to and including Excel 97. Is Excel 9 the same thing?  Does it matter?

I would be copying the range of cells in the Access created spreadsheet, say, A1, B1 and C1 and pasting them in the Invoice Template. It has come to my attention that the cells in each workbook would not be a one-for-one match, as I previously thought. The Access created workbook might be cells A1 through C1, but because the Invoice template is set up pictorially, the cells used in it would be different.  Is it possible to write the code to copy, say, cells A1, B1 and C1 in the Access created workbook and paste their values into cells D1, G3 and H8 respectively in the Invoice Template?

Finally, what would the code look like to save the Invoice Template as another name.

Your help on this is much appreciated!

Thanks, imnorie!  I really appreciate all you've done.   And if I wanted to put the copy/paste portion of the code in a button on the Invoice Template, is there a programmatic way to open a dialog box to navigate to and choose an already created Excel file?

I'm not quite sure what you mean.

The code opens an existing Excel file - the template file.

Would it be possible to split the code you wrote above into two parts, the Access part and the Excel part?  The file-to-be-copied-from would be generated in Access as you have it written by someone in a department other than accounting.  Then, I would take the code from the line "Now open the new file in Excel," and below, and put that in the on-click event of a newly created button on the Invoice Template workbook called, say, "Upload?"  My idea is to create the file-to-be-copied-from in Access, and load it into a directory on a shared drive that could be accessed later by the accounting office. The accounting office could then, at a later date, open the Invoice Template, click the "Upload" button, have a dialog box open that would allow them to browse to the file they wanted, choose the file, and then have the remainder of the code you wrote do the copy/paste processing.

Does that make sense?

That's what I thought you meant but wasn't 100% sure.

You would need to have the code in a separate sub that can be called by the button and from Access but that shouldn't be a problem.

I take it you wouldn't want the dialog if the code is called from Access?

That is correct. The dialog box would only need to be called from the Upload button on the Invoice Template.

I'll see what I can come up with.

Thanks, imnorie.  I don't want you to do this work without getting more points, though.  Can I post another question regarding this added info I'm requesting so that you can answer it and get the additional points?  If so, I will alert you when I have entered the new question.

Yes, that's fine.

Here is the name of the new question, VBA to choose and open an Excel file for import from a button on another Excel workbook.

Thanks again for your help!