open Excel file from Access using VBA code

Question

I have written code to select an Excel file when prompted but I now need to continue the code to open that file.  I need the first part because the file name will change from month to month but once I've selected the file I need to be able to open the file, delete some rows from the beginning, check the headers, save as.... 

This part of my code works:

Private Sub Command0_Click()

On Error Resume Next

Dim f As Object

Dim selectedFile As String

Dim fileReady As Boolean

Set f = Application.FileDialog(3)

f.AllowMultiselect = False

f.Show

selectedFile = f.SelectedItems(1)

On Error GoTo 0

If selectedFile = "" Or IsNull(selctedFile) Then Exit Sub

If Dir(selectedFile) = "" Then MsgBox "The thle path provided does not exist.": Exit Sub

If Right(selectedFile, 5) <> ".xlsx" Then MsgBox "Invalid file type!  Expecting .XLSX": Exit Sub

inWashSaleFile = selectedFile

End Sub

I tried adding the following code but it errors out:

Dim objXL As Object

Set objXL = GetObject(, selectedFile, "Excel.Application")

Set objXL = GetObject("selectedFile")

objXL.Application.Visible = True

objXL.Parent.Windows(1).Visible = True

objXL.Workbooks.Open "inWashSaleFile", True, False  'do I need this?

Answer 1

ciao John,

I think no need to use automation to achieve your task, try replace your code by this solution.

Into standard or general module :

Option Compare Database

Option Explicit

Private Const strPathPDF As String = "C\placeYourPathHere"   ' to be customized.

Public Function cmdFileDialog() As String

     Dim fDialog      As Object

     Dim strFile      As String

     Dim varFiles     As Variant

     Set fDialog = Application.FileDialog(3)

     With fDialog

       .Title = "select Excel files:"

       .AllowMultiSelect = True

        .Filters.Clear

        .Filters.Add "files PDF", "*.xlsx"

        .InitialFileName = strPathPDF

         If .Show Then

            strFile = .SelectedItems(1)

         Else

            strFile = vbNullString

         End If

     End With

    cmdFileDialog = strFile

  End Function

Public Sub openFileName(ByVal strFullPath As String)

CreateObject("Shell.Application").Namespace(0).ParseName(strFullPath).InvokeVerb "Open"

End Sub

Public Function FileExists(strPathFile As String) As Boolean

      On Error Resume Next

      FileExists = ((GetAttr(strPathFile) And vbDirectory) = 0)

End Function

the following code instead to open and work with your excel file :

Private Sub cmdOpen_Click()

If FileExists(Me.fullPathPDFfile) Then openFileName Me.fullPathPDFfile

End Sub

supposing to invoke it by a command button called cmdOpen placed into a form.

HTH.

Ciao, Sandro.

Answer 2

Hi John, I'm an independent adviser and will try to help.

I'm unclear on one point. You said you need to open the file, delete some rows, check some headers and save. Do you need to do this programmatically or do you need to look at the file to see what needs to be done?

If you just need to open the file to do the work, then you can do it simply:

Application.FollowHyperlink selectedFile

That will open the Excel file.

If you want to do this all programmatically then you need to detail what error message you are getting.

Here is sample code to open an Excel workbook so it can be worked on in code:

Dim objExcelApp As Excel.Application

Dim wb As Excel.Workbook

Set objExcelApp = Excel.Application

Set wb = objExcelApp.Workbooks.Open(selectedfile)

objExcelApp.Visible = True

Note, I have removed the quotes from selectedfile. Since that is a variable that contains the location of the file, its not a string and could be the cause of your problems.

Answer 3

You might like to take a look at BrowseDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates how to browse to and open or print a file, using the Windows API ShellExecute function.

If you are using 64 bit Access the function declaration will need to be changed to:

Declare PtrSafe Function ShellExecute& Lib "shell32.dll" Alias "ShellExecuteA" (ByVal _

hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal _

lpParameters As String, ByVal lpDirectory As String, ByVal nshowcm As Long)

Answer 4

Scott,

The Application.FollowHyperlink selectedFile works but I need to now select the first 4 rows of my now open workbook to delete those rows and move them up.

I tried the following:

Worksheets.Rows("1:4").Delete shift:=xlShiftUp

but it does nothing so I realize I need to add some additional coding.

Thanks for the help!

Answer 5

Hi John,

Ok, we are talking about two things here. If you are going to manually update the workbook, then you can use Application.Followhyperlink. But, as I said, if you want to programmatically modify the workbook, you have to continue to use Office Automation.

So you start off with

Dim objExcelApp As Excel.Application

Dim wb As Excel.Workbook

Set objExcelApp = Excel.Application

Set wb = objExcelApp.Workbooks.Open(selectedfile)

objExcelApp.Visible = True

So now you would want to do something like:

wb.Worksheets.Rows("1:4").Delete Shift:=xlShiftUp

You have to preface your Excel VBA commands by the variable set as the workbook. You might want to read up more on Office Automation with Excel.