Need help importing Excel files through VBA (1 Viewer)

miked1978

New member
Local time
Today, 01:00
Joined
May 22, 2020
Messages
25
I need some help handling an error. The below code takes a excel file and imports it into the Access database. It works fine as long as the file already exists but if something happens and that file doesn't exist, I get an error saying it can't find the object to (cant find object to delete)


Option Compare Database
Public Function ImportSheet()
Dim excelapp As Object
Set excelapp = CreateObject("excel.application")
Dim wb As Object

Set wb = excelapp.Workbooks.Open("FILEPATH\BOAT_001.xlsx")
Dim numberofrows As Integer
numberofrows = 1 + excelapp.Application.CountA(wb.worksheets("Sheet1").Range("A2:A10000"))
DoCmd.DeleteObject acTable, "
BOAT_001
"
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "
BOAT_001
", "
FILEPATH
\
BOAT_001
.xlsx", True, "Sheet1!A2:J" & numberofrows
wb.Close

'same code as above but for boat_002, etc
 

miked1978

New member
Local time
Today, 01:00
Joined
May 22, 2020
Messages
25
And sorry for the format. Just realized it came though like that
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:00
Joined
Mar 14, 2017
Messages
6,488
do you mean this line of code?
Code:
DoCmd.DeleteObject acTable, "BOAT"

If so, you could use this:
Code:
On Error Resume Next
CurrentDb.TableDefs.Delete "BOAT_001"
On Error Goto 0 'or resume whatever error handling you may already have in place
"[/CODE]?
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:00
Joined
Mar 14, 2017
Messages
6,488
Yeah the code tags messed up my post badly too - wonder what's going on today
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:00
Joined
Oct 29, 2018
Messages
18,972
Hi Mike. Welcome to AWF!

You described getting an error when a "file" does not exist. If that's the case, you can use the Dir() function to check if the file exists first before deleting the object.

Hope that helps...
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:00
Joined
Mar 14, 2017
Messages
6,488
Good call, I took it the table way, as there are so many people (I've discovered) around the world who refer to a table as a File plus I saw a table being deleted, guess the OP can clarify.
 

miked1978

New member
Local time
Today, 01:00
Joined
May 22, 2020
Messages
25
So that might have worked but my db keeps freezing up when I run it and I think it has more to do with how I'm importing the excel files.

Code:
Public Function ImportSheet()
Dim excelapp As Object
Set excelapp = CreateObject("excel.application")
Dim wb As Object

Set wb = excelapp.Workbooks.Open("FILEPATH\BOAT_001.xlsx")
Dim numberofrows As Integer
numberofrows = 1 + excelapp.Application.CountA(wb.worksheets("Sheet1").Range("A2:A10000"))
'DoCmd.DeleteObject acTable, "BOAT_001"
On Error Resume Next
CurrentDb.TableDefs.Delete "BOAT_001"
On Error GoTo 0
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "BOAT_001", "FILEPATH\BOAT_001.xlsx", True, "Sheet1!A2:J" & numberofrows
wb.Close
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:00
Joined
Mar 14, 2017
Messages
6,488
Question - is this line of code actually working for you now?

Code:
numberofrows = 1 + excelapp.Application.CountA(wb.worksheets("Sheet1").Range("A2:A10000"))
 

miked1978

New member
Local time
Today, 01:00
Joined
May 22, 2020
Messages
25
Question - is this line of code actually working for you now?

Code:
numberofrows = 1 + excelapp.Application.CountA(wb.worksheets("Sheet1").Range("A2:A10000"))


Yes I think so. I'm deleting the first row in each file. The 2nd row has the field names that I need.

*Edit - I say delete but its just not selecting it.
 

miked1978

New member
Local time
Today, 01:00
Joined
May 22, 2020
Messages
25
I don't know VBA so please excuse my ignorance. I got someone to help me with the code.
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:00
Joined
Mar 14, 2017
Messages
6,488
Yes I think so. I'm deleting the first row in each file. The 2nd row has the field names that I need.
Interesting. Seems like a double reference to application , and possibly missing a WorksheetFunction prior to Counta (but on that last point I know that sometimes undocumented, some of the worksheet functions will work right up against an application)
 

miked1978

New member
Local time
Today, 01:00
Joined
May 22, 2020
Messages
25
No I think i'm going to start it over. I think the way its importing the excel files is causing the db to hang up. I'm no longer getting an error message but its not importing any files over neither. Its just hanging and I have to go to the task manager and quit Access.
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:00
Joined
Mar 14, 2017
Messages
6,488
Debugging with F8 probably a good step, maybe isolate which line begins the Hang.
 

miked1978

New member
Local time
Today, 01:00
Joined
May 22, 2020
Messages
25
Would it be easier when importing the files it creates tables instead of linked files (tables) to Excel?

Here is what i'm trying to do:

1. bout 12 Excel files get downloaded to a file share folder each month. The names of the files aren't consistent but they all begin xxx_001. I have a script that changes the Excel file names to xxx_001.xlsx, xxx_002
.xlsx
, etc. This works fine

2. I need to get these files in Access so I can query the data that I need and this is the step I needed help on. Once I get them into Access I plan to query the data I need from the 12 files and put everything into 1 table. I know how to do this.
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:00
Joined
Mar 14, 2017
Messages
6,488
Would it be easier when importing the files it creates tables instead of linked files (tables) to Excel?
They both have their drawbacks. Linking is probably a heck of a lot easier up front, but then when you write queries you're stuck with how Access sees the Excel data, formats, errors, blanks, etc, which can be quite tricky IF those issues are present in your Excel data. Importing is probably harder up front - because any and every cell that doesn't match the destination table type and cannot be implicitly converted may cause an error and not always with a reference to the Excel row number.
 

Users who are viewing this thread

Top Bottom