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

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>

 

[eaddi]

I hope this explains what I am trying to do.





Delete ROWS
My Excel sheet contains a Summary and a Detailed section within the same sheet. I want to delete the Summary section in Red. I was trying to do this by saying find "Provision Date" and then delete all rows above the row that contain "Provision Date"

Header1	Header2	Header3	Header4	Header5
5/14/2018	5000	Magic Tv	West	N/a
5/14/2018	10000	Magic Tv	East	N/a
5/14/2018	2000	Magic Tv	South	N/a
5/14/2018	600	Magic Tv	North	N/a
5/13/2018	1500	Magic Tv	West	N/a
5/13/2018	900	Magic Tv	East	N/a
5/13/2018	1260	Magic Tv	South	N/a
5/13/2018	15000	Magic Tv	North	N/a
5/13/2018	0	Magic Tv	West	N/a
SHeader1	SHeader2	SHeader3	SHeader4	SHeader5	SHeader6	SHeader7	SHeader8	SHeader9
5/14/2018	200	Johnson	Pack001	West	Incoming	Taxable	30 days	N
5/14/2018	300	Smith	Pack03	North	Referral	Taxable	30 days	N
5/14/2018	500	Longbottom	Pack03	East	Incoming	Taxable	30 days	N
5/14/2018	150	Clark	Pack001	South	Incoming	Taxable	30 days	N
5/14/2018	-890	Fox	Pack001	West	Incoming	Notaxable	30 days	Y
5/14/2018	2000	James	Pack03	North	Incoming	Taxable	90 days	N
5/14/2018	500	Black	Pack001	North	Incoming	Taxable	30 days	N
5/14/2018	600	Whilken	Pack03	East	Referral	Taxable	30 days	N

<tbody>
</tbody>

Delete columns after Summary section has been deleted
Once my header data has been deleted and my detail data has shifted up so that the row that contains "Provision Date" is now in row 1. I need to unmerge my data. Assume SHeader 3 consist of the column C and D and SHeader6 consist of column H and I. What I need to do is unmerge the columns and then delete column D and column I, since both of them are blank.

SHeader1	SHeader2	SHeader3	SHeader4	SHeader5	SHeader6	SHeader7	SHeader8	SHeader9
5/14/2018	200	Johnson	Pack001	West	Incoming	Taxable	30 days	N
5/14/2018	300	Smith	Pack03	North	Referral	Taxable	30 days	N
5/14/2018	500	Longbottom	Pack03	East	Incoming	Taxable	30 days	N
5/14/2018	150	Clark	Pack001	South	Incoming	Taxable	30 days	N
5/14/2018	-890	Fox	Pack001	West	Incoming	Notaxable	30 days	Y
5/14/2018	2000	James	Pack03	North	Incoming	Taxable	90 days	N
5/14/2018	500	Black	Pack001	North	Incoming	Taxable	30 days	N
5/14/2018	600	Whilken	Pack03	East	Referral	Taxable	30 days	N

<tbody>
</tbody><colgroup><col span="9"></colgroup>

 

[Norie]

There are no rows that contain the text 'Provision Date'.

 

[eaddi]

SHeader1 would contain Provision Date. I apologize.