Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

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

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.
0
shanej
Asked:
shanej
  • 3
  • 2
1 Solution
 
NorieCommented:
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

    xlApp.Quit

    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

0
 
shanejAuthor Commented:
Which line of code in the MoveData sub opens a dialog box to find the file that was created by Access?
0
 
NorieCommented:
Line 11 and the line before it sets the current directorty which is where the dialog wil open.
0
 
shanejAuthor Commented:
Thank you. I'll check it out early next week.
0
 
shanejAuthor Commented:
Thanks again for the help and I appologize for the delay in getting back to you and awarding the points.
0

Featured Post

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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now