VBA to choose and open an Excel file for import from a button on another Excel workbook

Posted on 2012-08-17
Last Modified: 2012-09-02
Please reference the question, VBA for Opening a Variably Named File in Excel and Copying it's Workbook Contents to Another Workbook.

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.
Question by:shanej
    LVL 33

    Accepted Solution

    Put this in a module in the database.
    Option Compare Database
    Option Explicit
    Sub ExportAndMoveToTemplate()
    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:\Templates\Query - " & Format(Date, "ddmmmyyyy") & ".xls"
        ' do the export
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "State", strFileName
        ' now open the new file in Excel
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
        ' open the Invoice template file
        Set xlWBTemp = xlApp.Workbooks.Open("C:\Templates\Invoice Template.xls")
        xlApp.Run "MoveData", strFileName
        xlWBTemp.Close SaveChanges:=True
        Set xlWBTemp = Nothing
        Set xlApp = Nothing
    End Sub

    Open in new window

    Put this in a module in Invoice Template.xls, add a Forms button to a worksheet and assign the macro MoveData to it.
    Option Explicit
    Sub MoveData(Optional strFilename As Variant)
    Dim wb As Workbook
    Dim wbTemp As Workbook
    Dim ws As Worksheet
        If IsMissing(strFilename) Then
            ChDir "C:\"
            strFilename = Application.GetOpenFilename("Excel 97 Workbooks (*.xls),*.xls")
        End If
        If strFilename = "False" Then
            MsgBox "No file selected. Exiting."
            Exit Sub
        End If
        Set wb = Workbooks.Open(strFilename)
        Set wbTemp = ThisWorkbook
        ' copy/paste date between the 2 workbooks
        ' create reference to worksheet in new file - there should only be one.
        Set ws = wb.Worksheets(1)
        ws.Range("A1").Copy wbTemp.ActiveSheet.Range("D1")
        ws.Range("B1").Copy wbTemp.ActiveSheet.Range("G3")
        ws.Range("C1").Copy wbTemp.ActiveSheet.Range("H8")
        wbTemp.SaveAs "C:\Templates\Template - " & Format(Date, "ddmmmyyyy") & ".xls"
    End Sub

    Open in new window


    Author Comment

    Which line of code in the MoveData sub opens a dialog box to find the file that was created by Access?
    LVL 33

    Expert Comment

    Line 11 and the line before it sets the current directorty which is where the dialog wil open.

    Author Comment

    Thank you. I'll check it out early next week.

    Author Closing Comment

    Thanks again for the help and I appologize for the delay in getting back to you and awarding the points.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now