Hello
I am trying to open and modify an Excel 2016 file using VBA in Access 2016. I pieced together the code below, but I keep getting "Compile Error: Sub or Function not defined" at the bolded section of the code below. Does anyone know how to correct this error or if there is a better code to use?
Thanks in advance
<tbody>
</tbody>
I am trying to open and modify an Excel 2016 file using VBA in Access 2016. I pieced together the code below, but I keep getting "Compile Error: Sub or Function not defined" at the bolded section of the code below. Does anyone know how to correct this error or if there is a better code to use?
Thanks in advance
Sub OpenFileWithShell() Dim Shex As Variant Dim strPath As String Dim strFileName As String Dim strFileType As String Dim strApplication As String Dim xRow As Integer Dim strSearch As String strPath = "D:\Source Files" & "" strFileName = "Inventory.xls" strFileType = Mid(strFileName, InStrRev(strFileName, ".")) Select Case strFileType 'Identify type of file and set application to use Case ".xls" strApplication = "Excel.exe " 'Note the trailing space Case ".docx" strApplication = "Winword.exe " 'Note the trailing space End Select 'Enclose path and filename in double quotes in case of spaces (previously omitted) VarMyFile = Shell(strApplication & Chr(34) & strPath & strFileName & Chr(34), vbNormalFocus) ' UserForm1.Show vbModeless 'Open as modeless if access to workheet is required. strSearch = "Provision Date" ' Assuming Total is in column C as your picture shows, but you can configure to search anywhere xRow = Range("A" & Rows.Count).End(xlUp).Row Range("$A1:A" & xRow).Select Selection.Find(What:=strSearch, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Select Range("A1:A" & ActiveCell.Row - 1).EntireRow.Delete Cells.Select Selection.UnMerge Range("D:E,H:H,K:K,L:L").Select Range("L1").Activate Selection.Delete Shift:=xlToLeft Range("A1").Select ActiveWorkbook.Save ActiveWindow.Close End Sub |
<tbody>
</tbody>
Last edited: