Opening and Modifying Excel with Access VBA

E

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

[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]
 
Last edited:
Sort by date Sort by votes
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"



[TABLE="class: cms_table, width: 477"]
<tbody>[TR]
[TD="width: 70, bgcolor: transparent"]Header1[/TD]
[TD="width: 70, bgcolor: transparent"]Header2[/TD]
[TD="width: 70, bgcolor: transparent"]Header3[/TD]
[TD="width: 70, bgcolor: transparent"]Header4[/TD]
[TD="width: 70, bgcolor: transparent"]Header5[/TD]
[TD="width: 70, bgcolor: transparent"][/TD]
[TD="width: 70, bgcolor: transparent"][/TD]
[TD="width: 70, bgcolor: transparent"][/TD]
[TD="width: 70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/14/2018[/TD]
[TD="bgcolor: transparent, align: right"]5000 [/TD]
[TD="bgcolor: transparent"]Magic Tv[/TD]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]N/a[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/14/2018[/TD]
[TD="bgcolor: transparent, align: right"]10000 [/TD]
[TD="bgcolor: transparent"]Magic Tv[/TD]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent"]N/a[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/14/2018[/TD]
[TD="bgcolor: transparent, align: right"]2000[/TD]
[TD="bgcolor: transparent"] Magic Tv[/TD]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]N/a[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/14/2018[/TD]
[TD="bgcolor: transparent, align: right"]600[/TD]
[TD="bgcolor: transparent"] Magic Tv[/TD]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]N/a[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/13/2018[/TD]
[TD="bgcolor: transparent, align: right"]1500[/TD]
[TD="bgcolor: transparent"] Magic Tv[/TD]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]N/a[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/13/2018[/TD]
[TD="bgcolor: transparent, align: right"]900[/TD]
[TD="bgcolor: transparent"] Magic Tv[/TD]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent"]N/a[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/13/2018[/TD]
[TD="bgcolor: transparent, align: right"]1260[/TD]
[TD="bgcolor: transparent"] Magic Tv[/TD]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]N/a[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/13/2018[/TD]
[TD="bgcolor: transparent, align: right"]15000[/TD]
[TD="bgcolor: transparent"] Magic Tv[/TD]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]N/a[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/13/2018[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"] Magic Tv[/TD]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]N/a[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SHeader1 [/TD]
[TD="bgcolor: transparent"]SHeader2[/TD]
[TD="bgcolor: transparent"]SHeader3[/TD]
[TD="bgcolor: transparent"]SHeader4[/TD]
[TD="bgcolor: transparent"]SHeader5[/TD]
[TD="bgcolor: transparent"]SHeader6[/TD]
[TD="bgcolor: transparent"]SHeader7[/TD]
[TD="bgcolor: transparent"]SHeader8[/TD]
[TD="bgcolor: transparent"]SHeader9[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/14/2018[/TD]
[TD="bgcolor: transparent, align: right"]200[/TD]
[TD="bgcolor: transparent"]Johnson[/TD]
[TD="bgcolor: transparent"]Pack001[/TD]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]Incoming[/TD]
[TD="bgcolor: transparent"]Taxable[/TD]
[TD="bgcolor: transparent"]30 days[/TD]
[TD="bgcolor: transparent"]N[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/14/2018[/TD]
[TD="bgcolor: transparent, align: right"]300[/TD]
[TD="bgcolor: transparent"]Smith[/TD]
[TD="bgcolor: transparent"]Pack03[/TD]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]Referral[/TD]
[TD="bgcolor: transparent"]Taxable[/TD]
[TD="bgcolor: transparent"]30 days[/TD]
[TD="bgcolor: transparent"]N[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/14/2018[/TD]
[TD="bgcolor: transparent, align: right"]500[/TD]
[TD="bgcolor: transparent"]Longbottom[/TD]
[TD="bgcolor: transparent"]Pack03[/TD]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent"]Incoming[/TD]
[TD="bgcolor: transparent"]Taxable[/TD]
[TD="bgcolor: transparent"]30 days[/TD]
[TD="bgcolor: transparent"]N[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/14/2018[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="bgcolor: transparent"]Clark[/TD]
[TD="bgcolor: transparent"]Pack001[/TD]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]Incoming[/TD]
[TD="bgcolor: transparent"]Taxable[/TD]
[TD="bgcolor: transparent"]30 days[/TD]
[TD="bgcolor: transparent"]N[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/14/2018[/TD]
[TD="bgcolor: transparent, align: right"]-890[/TD]
[TD="bgcolor: transparent"]Fox[/TD]
[TD="bgcolor: transparent"]Pack001[/TD]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]Incoming[/TD]
[TD="bgcolor: transparent"]Notaxable[/TD]
[TD="bgcolor: transparent"]30 days[/TD]
[TD="bgcolor: transparent"]Y[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/14/2018[/TD]
[TD="bgcolor: transparent, align: right"]2000[/TD]
[TD="bgcolor: transparent"]James[/TD]
[TD="bgcolor: transparent"]Pack03[/TD]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]Incoming[/TD]
[TD="bgcolor: transparent"]Taxable[/TD]
[TD="bgcolor: transparent"]90 days[/TD]
[TD="bgcolor: transparent"]N[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/14/2018[/TD]
[TD="bgcolor: transparent, align: right"]500[/TD]
[TD="bgcolor: transparent"]Black[/TD]
[TD="bgcolor: transparent"]Pack001[/TD]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]Incoming[/TD]
[TD="bgcolor: transparent"]Taxable[/TD]
[TD="bgcolor: transparent"]30 days[/TD]
[TD="bgcolor: transparent"]N[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]5/14/2018[/TD]
[TD="bgcolor: transparent, align: right"]600[/TD]
[TD="bgcolor: transparent"]Whilken[/TD]
[TD="bgcolor: transparent"]Pack03[/TD]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent"]Referral[/TD]
[TD="bgcolor: transparent"]Taxable[/TD]
[TD="bgcolor: transparent"]30 days[/TD]
[TD="bgcolor: transparent"]N[/TD]
[/TR]
</tbody>[/TABLE]




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.


[TABLE="width: 630"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SHeader1 [/TD]
[TD]SHeader2[/TD]
[TD] SHeader3[/TD]
[TD]SHeader4[/TD]
[TD]SHeader5[/TD]
[TD]SHeader6[/TD]
[TD]SHeader7[/TD]
[TD]SHeader8[/TD]
[TD]SHeader9[/TD]
[/TR]
[TR]
[TD="align: center"]5/14/2018[/TD]
[TD]200 [/TD]
[TD]Johnson [/TD]
[TD]Pack001 [/TD]
[TD]West [/TD]
[TD]Incoming [/TD]
[TD]Taxable [/TD]
[TD]30 days [/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: center"]5/14/2018[/TD]
[TD]300[/TD]
[TD]Smith[/TD]
[TD]Pack03[/TD]
[TD]North[/TD]
[TD]Referral[/TD]
[TD]Taxable[/TD]
[TD]30 days[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: center"]5/14/2018[/TD]
[TD]500[/TD]
[TD]Longbottom[/TD]
[TD]Pack03[/TD]
[TD]East[/TD]
[TD]Incoming[/TD]
[TD]Taxable[/TD]
[TD]30 days[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: center"]5/14/2018[/TD]
[TD]150[/TD]
[TD]Clark[/TD]
[TD]Pack001[/TD]
[TD]South[/TD]
[TD]Incoming[/TD]
[TD]Taxable[/TD]
[TD]30 days[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: center"]5/14/2018[/TD]
[TD]-890[/TD]
[TD]Fox[/TD]
[TD]Pack001[/TD]
[TD]West[/TD]
[TD]Incoming[/TD]
[TD]Notaxable[/TD]
[TD]30 days[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: center"]5/14/2018[/TD]
[TD]2000[/TD]
[TD]James[/TD]
[TD]Pack03[/TD]
[TD]North[/TD]
[TD]Incoming[/TD]
[TD]Taxable[/TD]
[TD]90 days[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: center"]5/14/2018[/TD]
[TD]500[/TD]
[TD]Black[/TD]
[TD]Pack001[/TD]
[TD]North[/TD]
[TD]Incoming[/TD]
[TD]Taxable[/TD]
[TD]30 days[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: center"]5/14/2018[/TD]
[TD]600[/TD]
[TD]Whilken[/TD]
[TD]Pack03[/TD]
[TD]East[/TD]
[TD]Referral[/TD]
[TD]Taxable[/TD]
[TD]30 days[/TD]
[TD]N[/TD]
[/TR]
</tbody><colgroup><col span="9"></colgroup>[/TABLE]
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
There are no rows that contain the text 'Provision Date'.:eek:
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
ACCESS, VBA: Fetch data from sql server and paste it into local table.
Replies
3
Views
2K
Micron
M
tjdrake
  • Solved
Multi-User Access DB Table
Replies
3
Views
1K
Micron
M
R
  • Solved
Late Binding: extract Access to Excel with formatting
2 3
Replies
22
Views
5K
roelandwatteeuw
R
S
  • Question Question
MS ACCESS VBA CODE - open up an excel file, copy paste, save and copy file
Replies
1
Views
6K
Colo
Colo
J
  • Solved
VBA Code Not Closing Excel Application
2
Replies
12
Views
4K
JohanGduToit
J

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

Share this page

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