Opening and Modifying Excel with Access VBA

eaddi

New Member
Joined
Apr 2, 2012
Messages
24
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




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:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
If you are running this code from Access VBA then Range doesn't mean anything, the same with Cells, Selection, ActiveWorkbook, ActiveWindow etc

Also, even if this was being run in Excel VBA you wouldn't want to be using Select/Selection/ActiveWorkbook etc.

Even if you fixed all the above the code is unlikely to work as you won't have a reference to Excel or the workbook you want to modify.

What you really should do is create an instance of Excel,
Code:
Set xlApp = CreateObject("Excel.Application")
then open the workbook in that instance.
Code:
Set xlWB = xlApp.Workbooks.Open(strPath & strFileName)
Once you have the reference to the workbook you can use your code to modify it.
 
Upvote 0
I was also thinking that you have a bad example you are working from. It really looks like something written for Excel in Excel and doesn't really show how to do this from Access at all, nor would you generally use Shell() for either of these purposes.
 
Upvote 0
I did write a portion of this in Excel. Then I searched for how to open Excel from Access and pieced together the code. I have a file that I need to modify before it can be automatically imported into Access. Can you point me to an example of a better code?
 
Upvote 0
you don't need an example

just use the code Norie gave you and do something small

open excel and show it and close excel

once that's working add code to create a new worksheet

once that's working add code to put your name in cell A1 and your age in cell A2 and save the workbook

once that's working add code to open the workbook you just saved and do

xRow = Range("A" & Rows.Count).End(xlUp).Row

but remember that just like Count is a member of Rows
worksheets are members of workbook and ranges are members of the worksheet

so just preface everything with the correct object or variable

you do this line by line, piece by piece and debug your code until it works



 
Upvote 0
just underlining and repeating:

so just preface everything with the correct object or variable

this may include using some with / end blocks in practice:
Code:
set XLApp = ...
set wb =  ...
set ws = ...
with ws
    .Cells(1,1) = ....
    .Cells(1,2) = ....
    .Cells(1,3) = ....
end with

wb.Save()
wb.Close(False)
XLApp.Quit()
also since you are running from access don't forget to both close the workbook and quit the application at the end.
 
Upvote 0
Ok, I modified my code and I am still stuck


Sub DeleteXLLines()
Dim Myexcel As Object
Dim Myworkbook As Object
Dim Mysheet As Object
Dim MyFile As String
MyFile = "D:\Source_Files\Inventory.xls"
Set Myexcel = CreateObject("Excel.Application")
Set Myworkbook = Myexcel.Workbooks.Open(MyFile)
Set Mysheet = Myworkbook.sheets("Inventory")
Mysheet.Find = "Provision Date"
Mysheet.Rows("A" & Rows.Count).End(xlUp).Row
Mysheet.Rows("$A1:A" & xRow).Select
Mysheet.Find(What:=strSearch, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
Mysheet.Rows("A1:A" & ActiveCell.Row - 1).EntireRow.Delete
Mysheet.Columns.Select
Mysheet.Columns.UnMerge
Mysheet.Columns("D:E,H:H,K:K,L:L").Select
Mysheet.Cell("L1").Activate
Mysheet.Delete Shift:=xlToLeft
Myworkbook.Close True
Set Mysheet = Nothing
Set Myworkbook = Nothing
Set Myexcel = Nothing
 
Upvote 0
It doesn't recognized this portion of the code. I don't think I am using the correct actions after Mysheet.



Mysheet.Find = "Provision Date"
Mysheet.Rows("A" & Rows.Count).End(xlUp).Row
Mysheet.Rows("$A1:A" & xRow).Select
Mysheet.Find(What:=strSearch, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
Mysheet.Rows("A1:A" & ActiveCell.Row - 1).EntireRow.Delete
Mysheet.Columns.Select
Mysheet.Columns.UnMerge
Mysheet.Columns("D:E,H:H,K:K,L:L").Select
Mysheet.Cell("L1").Activate
Mysheet.Delete Shift:=xlToLeft
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top