0

I have a form which as a textbox named "Core Serial Number"

I have a folder that contains two Excel spreadsheets, with the folder name, and the Excel spreadsheet name that are the same as the core serial number displayed in the text box.

e.g.

  • Core Serial Number Displayed in textbox on form = CAE540151
  • Folder located on A:\ Drive = CAE540151
  • Excel worksheet located in folder = CAE540151.

The form is linked to a master Excel spreadsheet, which is updated daily, and any data that is changed in the master Excel spreadsheet is replicated on the form.

What I would like to do is creating a command button on the form that will allow me to open the Excel worksheet located in the folder on A:\ drive that corresponds to the core serial number displayed in the textbox.

Is this possible? I am using Access 2013

shA.t
  • 16,580
  • 5
  • 54
  • 111
Andrew Wade
  • 1
  • 1

1 Answers1

0

Here is where I started: http://www.ozgrid.com/forum/showthread.php?t=17905

I had a button named Command3 and a textbox named Text1. 

Private Sub Command3_Click()

    Dim strLocation As String

    strLocation = "A:\CAE540151\" & Trim(Text1.Value)

    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Set xlApp = New Excel.Application

    With xlApp
        .Visible = True
        Set xlWB = .Workbooks.Open(strLocation, , False)
    End With

End Sub

The textbox contained the full name of the file (ie CAE540151.xlsx).

You will need to have a reference to Microsoft Excel. You can set that from the VBA menu by choosing Tools and then References.

dev1998
  • 882
  • 7
  • 17
  • For handling and building paths I recommend using the Microsoft Scripting Library, See http://stackoverflow.com/a/27253817/380384 – John Alexiou Jul 27 '15 at 20:02