Access VBA code to open & sort excel sheet (1 Viewer)

bn1974

Registered User.
Local time
Yesterday, 23:02
Joined
Apr 29, 2016
Messages
17
Have an excel sheet that I want Access to open, delete the first 2 rows and then sort (remove all rows where column C does not equal "A0C6")

I can get the sorting code to work in excel, but not access.

I believe the bolded row is where I am getting the error.
Would appreciate any help!
Thanks a bunch!!

Code:
Function CleanupFile()
Dim oExcel As Object
Dim oBook As Object
Dim oRange As Object
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
Set oBook = oExcel.Workbooks.Open("H:\AER\IMPORTS\Inactive_Well_Licence_List.xlsx")
oBook.Sheets(1).Rows(1).Delete
oBook.Sheets(1).Rows(1).Delete
AutoFilterMode = False
With oRange("C2", oRange("C" & Rows.Count).End(xlUp))
.AutoFilter 1, "<>A0C6"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
AutoFilterMode = False
oBook.Save
oBook.Close
oExcel.Quit
End Function
Code:
 

JHB

Have been here a while
Local time
Today, 07:02
Joined
Jun 17, 2012
Messages
7,732
oRange is not belonging to MS-Access therefore you need a reference to the Excel object, both in this code but also in some of the others, (AutoFilterMode).
 

bn1974

Registered User.
Local time
Yesterday, 23:02
Joined
Apr 29, 2016
Messages
17
Can you please tell me how to reference the Excel sheet? I'm a VBA novice. :)
 

JHB

Have been here a while
Local time
Today, 07:02
Joined
Jun 17, 2012
Messages
7,732
Okay, but you've the reference in some of your codelines.
Code:
[B][COLOR=Red]oBook.Sheets(1).[/COLOR][/B]AutoFilterMode = False
With [B][COLOR=Red]oBook.Sheets(1).[/COLOR][/B]Range("C2", [B][COLOR=Red]oBook.Sheets(1).[/COLOR][/B]Range("C" & [B][COLOR=Red]oBook.Sheets(1).[/COLOR][/B]Rows.Count).End(xlUp))
 

bn1974

Registered User.
Local time
Yesterday, 23:02
Joined
Apr 29, 2016
Messages
17
Hmm.
Getting this error: Object doesn't support this property or method
 

JHB

Have been here a while
Local time
Today, 07:02
Joined
Jun 17, 2012
Messages
7,732
Which codeline, show it?
Else post your database and Excel-sheet, zip it.
 

bn1974

Registered User.
Local time
Yesterday, 23:02
Joined
Apr 29, 2016
Messages
17
With oBook.Sheets(1)("C2", oBook.Sheets(1)("C" & oBook.Sheets(1).Rows.Count).End(xlUp))
 

bn1974

Registered User.
Local time
Yesterday, 23:02
Joined
Apr 29, 2016
Messages
17
Thanks for your help - I really appreciate it!
 

JHB

Have been here a while
Local time
Today, 07:02
Joined
Jun 17, 2012
Messages
7,732
With oBook.Sheets(1)("C2", oBook.Sheets(1)("C" & oBook.Sheets(1).Rows.Count).End(xlUp))
But that is not the codeline I showed you! :confused:
 

bn1974

Registered User.
Local time
Yesterday, 23:02
Joined
Apr 29, 2016
Messages
17
You are right - sorry, I missed a bit.
Here is what I have now:

With oBook.Sheets(1).Range("C2", oBook.Sheets(1).Range("C" & oBook.Sheets(1).Rows.Count).End(xlUp))

And I am getting this error: Application-defined or object-defined error
 

JHB

Have been here a while
Local time
Today, 07:02
Joined
Jun 17, 2012
Messages
7,732
You are missing the Excel Object Library.
Open the code window, from the menu-line:
Tools->References, mark the "Microsoft Excel xx Object Library" in the list.
I've attached an example which run, (remember to change the path for the Excel-file to fit yours).
 

Attachments

  • Test1.zip
    28.9 KB · Views: 408

JANR

Registered User.
Local time
Today, 07:02
Joined
Jan 21, 2009
Messages
1,623
Code:
With oBook.Sheets(1).Range("C2", oBook.Sheets(1).Range("C" & oBook.Sheets(1).Rows.Count).End([B][COLOR="Red"]xlUp[/COLOR][/B]))

If you are going to use late binding, you have to tell Access what the value of xlUp is. This is an Excel spesific constant.

either.

Const xlUp As Long = -4162

or declare it as a Public constant if you are going to use it somewhere else in your codes.

Public Const xlUp As long = -4162

JanR
 

bn1974

Registered User.
Local time
Yesterday, 23:02
Joined
Apr 29, 2016
Messages
17
Thank you, thank you, thank you!! It is working!!

I've run into a small snag, though. The spreadsheet that I am downloading has a number (stored as text to preserve leading 0's) in the first column. Unfortunately, it also has a space before the number. Is there a way to delete the space? I need this number to match the ones in my DB.

Thanks for everything!
 

JHB

Have been here a while
Local time
Today, 07:02
Joined
Jun 17, 2012
Messages
7,732
Good you got it.
You're welcome, good luck. :)
 

Users who are viewing this thread

Top Bottom