Re: Method 'Open' of object 'Workbooks' failed

[Ken Snell]

Not sure why you're getting this problem. However, change this code line:

Dim objExcel As Excel.Application, objWorkbook As Excel.Workbook



to this:

Dim objExcel As Object, objWorkbook As Object



Let's see if that clears up that problem.

Also, because you're using an .xlsx EXCEL file, you need to change this
line:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames, colWorksheets(lngCount)
& "$"



to this:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
strTable, strPathFile, blnHasFieldNames, colWorksheets(lngCount)
& "$"

--

Ken Snell
http://www.accessmvp.com/KDSnell/

 

[Ken Snell]

When the code "breaks", click Debug on the popup window. Hover the cursor
over the strPathFile variable and see what value it has. Is that value the
one you want for the path and filename? Be sure that there are no unwanted
spaces or line break (line feed / carriage return) characters in the string.

The error you're getting now indicates that something is wrong with one of
the arguments in the Open method, and ACCESS isn't able to open the file.

I just tested the code structure/logic in ACCESS 2007, and it's working
correctly for me.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Dear Ken,

Thank you for your quick reply.
You were right, It did solve the problem "Method 'Open' of object
'Workbooks'
failed", but
now this error message keeps popping up:
Run-time error '1004':
Unable to get the Open property of the Workbooks class

Still at the same line as before:

' Open the EXCEL file and read the worksheet names into a collection
Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPathFile, blnReadOnly,
strPassword) <<<<<<<
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount

Any Ideas?

Not sure why you're getting this problem. However, change this code line:

Dim objExcel As Excel.Application, objWorkbook As Excel.Workbook

to this:

Dim objExcel As Object, objWorkbook As Object

Let's see if that clears up that problem.

Also, because you're using an .xlsx EXCEL file, you need to change this
line:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames,
colWorksheets(lngCount)
& "$"

to this:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
strTable, strPathFile, blnHasFieldNames,
colWorksheets(lngCount)
& "$"

Hello first of all...I am new to this forum,

[quoted text clipped - 84 lines]

Regards
Michael

 

[Ken Snell]

Let's try an experiment.

Copy the file and put the copy of the file in your C drive folder, such that
the path to the file is

"C:\21.12.2009.CompareUser.xlsx"

Change your VBA code accordingly, and then run the code. Does it work
correctly?

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Ken,

When I hover over strPathFile it shows the correct Filename: "C:\Users\
Michael.Speicher\Documents\Comparison\21.12.2009.CompareUser.xlsx"
For me it just doesn't make much sense why it's not working :S
I mean why does it work by you and not by me?
Could it be that I forgot a certain reference?
I would kind of doubt that...I mean the code seems correct to me.
It just won't do it :S
Do you know how I can fix that?

Cold Regards from Switzerland

When the code "breaks", click Debug on the popup window. Hover the cursor
over the strPathFile variable and see what value it has. Is that value the
one you want for the path and filename? Be sure that there are no unwanted
spaces or line break (line feed / carriage return) characters in the
string.

The error you're getting now indicates that something is wrong with one of
the arguments in the Open method, and ACCESS isn't able to open the file.

I just tested the code structure/logic in ACCESS 2007, and it's working
correctly for me.

Dear Ken,

[quoted text clipped - 48 lines]

Regards
Michael

 

[Ken Snell]

Are you able to manually open that EXCEL file when it's in the C drive
folder?

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Unfortunately, it doesn't :S
I still get the same error message.

Let's try an experiment.

Copy the file and put the copy of the file in your C drive folder, such
that
the path to the file is

"C:\21.12.2009.CompareUser.xlsx"

Change your VBA code accordingly, and then run the code. Does it work
correctly?

Ken,

[quoted text clipped - 25 lines]

Regards
Michael

 

[Ken Snell]

I don't have an answer for you at this time. Interesting problem.

Are you running ACCESS and EXCEL using locally installed programs (on your
computer, not on a network to which you're connecting via CITRIX or some
other method)?

Try creating a brand-new EXCEL file and use that file for the code's import.
Does the newly created file work?

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Yes, I can open it.

Are you able to manually open that EXCEL file when it's in the C drive
folder?

Unfortunately, it doesn't :S
I still get the same error message.

[quoted text clipped - 15 lines]

Regards
Michael

 

[Ken Snell]

Found the problem. You left out some arguments in the .Open method.

This line of code that you have:

Set objWorkbook = objExcel.Workbooks.Open(strPathFile, blnReadOnly,
strPassword)


Needs to be changed to this:

Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , _
blnReadOnly, , strPassword)


Notice the extra commas with no variable/value in between them? They're
needed.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

I don't have an answer for you at this time. Interesting problem.

Are you running ACCESS and EXCEL using locally installed programs (on your
computer, not on a network to which you're connecting via CITRIX or some
other method)?

Try creating a brand-new EXCEL file and use that file for the code's
import. Does the newly created file work?

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Yes, I can open it.

Are you able to manually open that EXCEL file when it's in the C drive
folder?

Unfortunately, it doesn't :S
I still get the same error message.
[quoted text clipped - 15 lines]
Regards
Michael

 

[Ken Snell]

When you set the blnHasFieldNames variable to True, then ACCESS expects the
first row (row 1) of data in EXCEL worksheet to contain the field names.
Those names must match exactly the field names in your destination table,
and they must be in the same order for worksheet and table. I don't know why
you were having the import problems you noted initially, but it appears that
your setup is working correctly now?

--

Ken Snell
http://www.accessmvp.com/KDSnell/

But when I delete all the records, change the column names back to F1,
F2...
and clicked on run the same error message poped up: Field 'userid' doesn't
exist in Destination Table 'MemberInfo'
And NOW, I changed them back to userid, username....and clicked on run, I
got
all the values in the correct columns. Why didn't it do it the first time?
If you don't understand what I mean, I'd be happy to show you a picture

Great! That worked! Amazing how a couple of commas can make a project not
work!
Now that that finally works something else isn't working now...my file has
7
columns...userid, username, email, homepage, skype, usertitle and
birthday...
here's the problem:

DoCmd.TransferSpreadsheet acImportDelim, acSpreadsheetTypeExcel12, _
strTable, strPathFile, blnHasFieldNames,
colWorksheets(lngCount)
& "$"

Field 'F1' doesn't exist in Destination table 'MemberInfo'

Since Access doesn't recognize any tables it automatically names the first
column F1, F2, F3, F4...right?
So I changed the column names on the MemberInfo Table to F1, F2, F3, F4,
F5,
F6, F7 when I clicked on run, it suddently said:

Field 'userid' doesn't exist in Destination Table 'MemberInfo'

So ich changed it back to userid, saved the table, closed it, ran the
problem
and than it showed

Field 'usename' doesn't exist in Destination Table 'MemberInfo'

And when I went on the table there was the userid in that column, so I
inserted all the correct column names again and clicked on Run, then this
poped up:

Field 'F1' doesn't exist in Destination Table 'MemberInfo'

But now, here's the strange thing :S
I have the values I need in the first row
than from row 2 - 260 it's empty, enethough it should show some
information
AND THAN from row 261 - 571, the information that is needed are in the
rows...
why are the rows from 2 - 260 empty? and why do I still get that error
message :S

Again thanks for helping me!

Found the problem. You left out some arguments in the .Open method.

[quoted text clipped - 16 lines]

Regards
Michael

 

[Ken Snell]

When you do the import, are you importing to an existing table? Or are you
creating a new table each time?

If you're importing to an existing table, I assume that the field names are
the same names as the first row in the EXCEL spreadsheet?

Also, do one more test. Open the EXCEL spreadsheet, click on cell A1, then
press Ctrl+End. Does the cursor go to the most rightward cell with data? Or
does it go to a cell beyond the last column or row of data?

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Yeah, it only works when I click on Run and than the error message pops
up,
so I change the column names to F1, F2... click on Run again, error
message
pops up again, change the column names back to userid, username... click
on
Run 1 final time and all the value are where they're supposed to be. So I
have to change the column names about 3 times, only then will it work
correctly, just wondering why I always have to do that. Takes quite some
time
until I have all the value where they should be. Would be nice not to have
to
change the column names like 3 times.
So to your question, yeah and no, if I would have to changed the column
names
3 times, then it would work perfectly.

Best Regards from cold Switzerland

When you set the blnHasFieldNames variable to True, then ACCESS expects
the
first row (row 1) of data in EXCEL worksheet to contain the field names.
Those names must match exactly the field names in your destination table,
and they must be in the same order for worksheet and table. I don't know
why
you were having the import problems you noted initially, but it appears
that
your setup is working correctly now?

But when I delete all the records, change the column names back to F1,
F2...

[quoted text clipped - 56 lines]

Regards
Michael

 

[Ken Snell]

Using Ctrl+End tells you the limit of the .UsedRange property for the EXCEL
spreadsheet. ACCESS uses that property in order to identify how many rows
and columns are to be imported. Often, when people edit spreadsheets, they
sometimes enter/edit/delete data from a column or row outside the "desired"
row/column block, and that deletion (or edit, etc.) sets the .UsedRange
property to that cell. Then, when ACCESS tries to import the spreadsheet, it
sees more columns than you intended, and that extra column isn't in your
table, so ACCESS looks for a field named F1 in your table into which it
wants to import that extra column's data.

I was thinking that perhaps this situation existed on your spreadsheet, and
that that was the cause of the error that you're getting until you edit
field names.

Another common cause of this type of problem is when there are hidden
columns on the spreadsheet. But it doesn't appear that you have this
problem.

All worksheets in the file have the same field names, in the same order,
correct?

Hmmm... I have one last thought and experiment to try. Open one of the EXCEL
files that you have NOT modified yet. Click on cell A1 on one of the
spreadsheets. Then click on the arrow to the right of the Name Box. Do you
see a name there that is the same as the spreadsheet's name? If yes, click
on that name. Which cells are highlighted as a block on the spreadsheet?
What may be happening is that you have a Range in the workbook file that has
the same name as the spreadsheet, but the Range does not include the first
row of the spreadsheet. TransferSpreadsheet actually imports a Range object,
not a worksheet object; when no Range is found with the name you specify in
the TransferSpreadsheet's last argument, then ACCESS imports the
spreadsheet.

Let me know if the above is the situation in your workbook file.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

I am importing to an existing table and the field names are the same names
as
the first row in the Excek spreadsheet!
Well, when I click on cell A1 and then press Ctrl+End, it goes to cell
G261,
which is the last cell of the spreadsheet. My spreadsheet has 261 rows and
7
columns (G).
Why? Why is that important?

When you do the import, are you importing to an existing table? Or are you
creating a new table each time?

If you're importing to an existing table, I assume that the field names
are
the same names as the first row in the EXCEL spreadsheet?

Also, do one more test. Open the EXCEL spreadsheet, click on cell A1, then
press Ctrl+End. Does the cursor go to the most rightward cell with data?
Or
does it go to a cell beyond the last column or row of data?

Yeah, it only works when I click on Run and than the error message pops
up,

[quoted text clipped - 30 lines]

Regards
Michael

 

[Ken Snell]

Yes, that is the problem. The Range object does not include the first row of
data, so ACCESS doesn't see the 'field names' in the spreadsheet until you
modify the Range's scope to include that first row in the spreadsheet.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Ken,

To your question, yes, they have the same field names, in the same order.

Well here's the thing, the cell A1 by me is userid and has an arrow next
to
it as you said.
This is what I have:

A1 (Arrow) fx userid

Ken wrote: Do you see a name there that is the same as the spreadsheet's
name?
Answer: No. When I click on that Arrow, Table1, appears. The File is
called
21.12.2009.CompareUser and the sheet with to data in it is called Sheet1
Ken: If yes, click on that name. Which cells are highlighted as a block on
the spreadsheet?
Answer: Row 2 until Row 261 (last row of file), so from cell A2 : G261
(R261
x G7). The first row, the row with the filed names in it (userid,
username,
email, homepage, skype, usertitle, and birthday), is not highlighted as a
block on the spreadsheet!
I guess that's the problem, ísn't it?

Using Ctrl+End tells you the limit of the .UsedRange property for the
EXCEL
spreadsheet. ACCESS uses that property in order to identify how many rows
and columns are to be imported. Often, when people edit spreadsheets, they
sometimes enter/edit/delete data from a column or row outside the
"desired"
row/column block, and that deletion (or edit, etc.) sets the .UsedRange
property to that cell. Then, when ACCESS tries to import the spreadsheet,
it
sees more columns than you intended, and that extra column isn't in your
table, so ACCESS looks for a field named F1 in your table into which it
wants to import that extra column's data.

I was thinking that perhaps this situation existed on your spreadsheet,
and
that that was the cause of the error that you're getting until you edit
field names.

Another common cause of this type of problem is when there are hidden
columns on the spreadsheet. But it doesn't appear that you have this
problem.

All worksheets in the file have the same field names, in the same order,
correct?

Hmmm... I have one last thought and experiment to try. Open one of the
EXCEL
files that you have NOT modified yet. Click on cell A1 on one of the
spreadsheets. Then click on the arrow to the right of the Name Box. Do you
see a name there that is the same as the spreadsheet's name? If yes, click
on that name. Which cells are highlighted as a block on the spreadsheet?
What may be happening is that you have a Range in the workbook file that
has
the same name as the spreadsheet, but the Range does not include the first
row of the spreadsheet. TransferSpreadsheet actually imports a Range
object,
not a worksheet object; when no Range is found with the name you specify
in
the TransferSpreadsheet's last argument, then ACCESS imports the
spreadsheet.

Let me know if the above is the situation in your workbook file.

I am importing to an existing table and the field names are the same
names
as

[quoted text clipped - 23 lines]

Regards
Michael

 

[Ken Snell]

You need to modify that Range's scope in the EXCEL spreadsheet. Delete the
Range and then recreate the Range the way you want it to be in terms of the
desired rows/columns.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

So, is there a way to change that? Or not?

Yes, that is the problem. The Range object does not include the first row
of
data, so ACCESS doesn't see the 'field names' in the spreadsheet until you
modify the Range's scope to include that first row in the spreadsheet.

Ken,

[quoted text clipped - 70 lines]

Regards
Michael

 

[Ken Snell]

I am not an expert regarding EXCEL, so you may want to post a question in
the EXCEL newsgroup about how to fix the range. Otherwise, change the name
of the current range, then create a new range with name of Table1 that
includes all the rows on the spreadsheet.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

OK, theres already a Range in the Spreadsheet, it's called Table1 and I
can't
delete it for some reason. The only thing that I can do is change the name
of
the that Range.
I can add another Range, but I still have the same problem :S.

You need to modify that Range's scope in the EXCEL spreadsheet. Delete the
Range and then recreate the Range the way you want it to be in terms of
the
desired rows/columns.

So, is there a way to change that? Or not?

[quoted text clipped - 8 lines]

Regards
Michael