Opening and Modifying Excel with Access VBA

[eaddi]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]


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

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Norie]

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,

Set xlApp = CreateObject("Excel.Application")

then open the workbook in that instance.

Set xlWB = xlApp.Workbooks.Open(strPath & strFileName)

Once you have the reference to the workbook you can use your code to modify it.

 

[xenou]

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.

 

[eaddi]

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?

 

[james_lankford]

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

 

[xenou]

just underlining and repeating:

so just preface everything with the correct object or variable

this may include using some with / end blocks in practice:

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.

 

[xenou]

Note another example just posted in a similar post:
https://www.mrexcel.com/forum/micro...-excel-rows-using-access-via-button-code.html

 

[eaddi]

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

 

[Norie]

Stuck how?

 

[eaddi]

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