[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-08-13
18
Medium Priority
?
362 Views
Last Modified: 2012-08-17
I have an Excel workbook that is formatted to look like an Invoice (Invoice  Template).  I also have an Access application that is creating an Excel file - the name of which is both date and time stamped - based on an Access query.  

What I would like to do is create a button on the Invoice Template that would run VBA that would copy the contents of a range of cells in the Access created Excel workbook (Target), and paste the data into the Invoice Template.  How would I write the code to loop through the cells of the target file and paste the contents into the Invoice Template, assuming all the cells were a one-to-one match?

The main stumbling block for me is dealing with the target Excel workbook because its name would always be a variable because of the date/time stamp naming convention.
0
Comment
Question by:shanej
  • 9
  • 9
18 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 38289857
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.
0
 

Author Comment

by:shanej
ID: 38289904
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.
0
 
LVL 35

Expert Comment

by:Norie
ID: 38290132
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:shanej
ID: 38290155
I'm not sure I'm following.  Can you give me an example?
0
 
LVL 35

Expert Comment

by:Norie
ID: 38290355
Can you post the code you are using to create the new Excel file?
0
 

Author Comment

by:shanej
ID: 38292499
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.
0
 
LVL 35

Expert Comment

by:Norie
ID: 38292661
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

Open in new window

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

Author Comment

by:shanej
ID: 38298620
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!
0
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 38298670
As far as I know version 9 of Excel is 97.

It's straightforward to write code to copy from one cell to another.

Here's a quick example, which isn't perfect because I don't know the sheet name in the template workbook.
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
    ' create reference to worksheet in new file - there should only be one.

    Set xlWS = xlWB.Worksheets(1)

    xlWS.Range("A1").Copy xlWBTemp.ActiveSheet.Range("D1")
    xlWS.Range("B1").Copy xlWBTemp.ActiveSheet.Range("G3")
    xlWS.Range("C1").Copy xlWBTemp.ActiveSheet.Range("H8")

    xlWBTemp.SaveAs "C:\Template - " & Format(Date, "ddmmmyyyy") & ".xls"

    ' close workbooks and Excel
    xlWBTemp.Close SaveChanges:=True

    Set xlWBTemp = Nothing

    xlWB.Close SaveChanges:=False

    Set xlWB = Nothing

    xlApp.Quit

    Set xlApp = Nothing

Open in new window

0
 

Author Closing Comment

by:shanej
ID: 38298734
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?
0
 
LVL 35

Expert Comment

by:Norie
ID: 38298771
I'm not quite sure what you mean.

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

Author Comment

by:shanej
ID: 38298876
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?
0
 
LVL 35

Expert Comment

by:Norie
ID: 38298884
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?
0
 

Author Comment

by:shanej
ID: 38298900
That is correct. The dialog box would only need to be called from the Upload button on the Invoice Template.
0
 
LVL 35

Expert Comment

by:Norie
ID: 38298910
I'll see what I can come up with.
0
 

Author Comment

by:shanej
ID: 38306675
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.
0
 
LVL 35

Expert Comment

by:Norie
ID: 38306794
Yes, that's fine.
0
 

Author Comment

by:shanej
ID: 38306959
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!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question