Access 2010 - Win 7 problem opening Excel (1 Viewer)

BUzzbomb23

New member
Joined
Dec 20, 2019
Messages
6
We're switching over from Access 2003 to Access 2007 & have a mix. of Windows 7 (32 bit) & Windows 10 (64 bit) machines. I'm having problems converting some mdb databases to accdb, where code works on the several machines, but not on my Win 7 (32 bit) work laptop.


The problem occurs when I try to open an Excel spreadsheet within Access VBA. The spreadsheet opens ok but on Win 7 (32 bit) machines, it gives a 1004 error when I try to create a reference to the workbook.


I've replicated the error on a 2nd Win7 (32 bit) laptop... & code has worked fine in the past with Access 2003 & on other machines (inc my home Win 7 (64-bit).


I'm stumped by the problem - any help much appreciated! :banghead:

Ivor



Code:
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

 Set xlApp = CreateObject("Excel.Application")

 xlApp.Workbooks.Open "[path to xlsx file]"
 xlApp.Visible = True
    
 Set xlbook = xlApp.Workbooks.Item(1) 'Error occurs here...
 

Ranman256

Registered User
Joined
Apr 9, 2015
Messages
3,426
In VBE,(alt-f11) , tools, references,
See if the is a ref checked but labeled MISSING.

Ugrading wii remove the old excel driver, so just uncheck the missing ref, then scroll down and check the new version of Excel.
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,016
FWIW I still use two on my mdb's in 2007 without any problems.?

So unless you are looking for newer features, you could just leave it as it is.?
 

BUzzbomb23

New member
Joined
Dec 20, 2019
Messages
6
Thanks Ranman256. The References are fine - none missing & all the same versions as works from Access 2003.

Thanks Gasman - my preferred option would have been to have left everything on Access 2003... (I much prefer the interface to that in 2010), but it's a corporate decision. Access 2003 is being taken off the network on 31st Dec 2019 and Access 2010 is going the following year.

I've been using the program on & off since 1991, so I'm going to be *really* stuck...

With an .mdb database, the code works when run from Access 2003, but fails when I open & run the same file/code from Access 2010.
 

vba_php

Banned
Joined
Oct 6, 2019
Messages
1,974
Code:
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

 Set xlApp = CreateObject("Excel.Application")

 xlApp.Workbooks.Open "[path to xlsx file]"
 xlApp.Visible = True
    
  Set xlbook = xlApp.Workbooks.Item(1) 'Error occurs here...
have you tried these?
Code:
 Set xlApp = new excel.application
.......
Set xlbook = xlApp.Workbooks.open("path")
Code:
 Set xlApp = CreateObject("Excel.Application")
.......
Set xlbook = xlApp.Workbooks.open("path")
 

BUzzbomb23

New member
Joined
Dec 20, 2019
Messages
6
Thanks Adam. I get the same 1004 error at the "Set xlbook..." line.

In both cases, the spreadsheet opens & then the error occurs.
 

eshai

Registered User
Joined
Jul 14, 2015
Messages
159
did you take the db from x64 and run it on x32?
look if the spreadsheet is protected
when you copy a file from one pc to another the file get protected
 

BUzzbomb23

New member
Joined
Dec 20, 2019
Messages
6
Problem occurs irrespective of whether the db was created on x64 or x32 & occurs with any Excel spreadsheet. I and an IT colleague have tried creating db's from scratch & with different spreadsheets. Protection isn't an issue - I often use read-only templates to prevent me from accidentally messing up a file & "protect/unprotect" is useful for locking down stuff.
On the plus side, I've been given the go-ahead to put in a purchase request for a "high performance" 64-bit Win 10 laptop...
 

eshai

Registered User
Joined
Jul 14, 2015
Messages
159
Db that created on x64 won't work on x32 vs varsia
The dll's are different
When i worth protected i meant block by windows defender you can see it on file properties
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,533
OK, trying to open the Excel workbook through VBA and an Excel App object fails. Can you directly open the same workbook through the Windows GUI? If it DOES open, do you get a yellow "not trusted" bar on the spreadsheet? Do that specifically from the problem user's machine, not from your own system.

There are a gazillion things in the registry related to handling "trust" issues and error 1004 seems to be a generic "can't open that" error that doesn't give more details. Which opens up a whole boatload of possible causes. The test for directly opening the file would eliminate such things as permissions (which can be user-specific) and trusts (which can also be user-specific.)
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,326
Sorry eshai but that first comment isn't correct.

ACCDB files will work in both 32-bit and 64-bit Access. If you use APIs you may need to check these and possibly modify them slightly but otherwise there should be no issues using a dB created in 64-bit on 32-bit or vice versa.
However ACCDE files will only run in the bitness used to create them

No idea what you meant by the last sentence you wrote. Think the meaning got lost due to a language issue
 

eshai

Registered User
Joined
Jul 14, 2015
Messages
159
Sorry eshai but that first comment isn't correct
My mistake I thought was working on ACCDE
For the second thing, you may see a message in the file properties at the bottom "this file came from another computer and might be blocked to help protect this computer"
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,326
The second thing is seen if the file isn't in a trusted location. If so, no code will run until the project is marked as trusted.
 

eshai

Registered User
Joined
Jul 14, 2015
Messages
159
The second thing is seen if the file isn't in a trusted location. If so, no code will run until the project is marked as trusted.
Just understand if we are talking about the same thing
Right click on the file itself not within Access
By the way I had a problem about two weeks ago. on all my computers (11) Windows 10(x64) and Office 2016 are all installed from the same disk and the same serial number
I took a database from one of the computers and opened it on my laptop and got an error "couldn't read the vba code..." Clicking OK for this message deletes all vba code
After checking with Microsoft I was told that the vb7 dll as Has changed:confused:
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,326
Just understand if we are talking about the same thing
Right click on the file itself not within Access
By the way I had a problem about two weeks ago. on all my computers (11) Windows 10(x64) and Office 2016 are all installed from the same disk and the same serial number
I took a database from one of the computers and opened it on my laptop and got an error "couldn't read the vba code..." Clicking OK for this message deletes all vba code
After checking with Microsoft I was told that the vb7 dll as Has changed:confused:
If the file isn't trusted, it doesn't matter how you load the file, the yellow message bar will be shown and no code will run until its trusted.

The message "can't read the vba code... " indicates severe corruption. The chances are that database is no longer recoverable unless you use specialist recovery software.

If you have installed Office 2016 on more computers than you have licenses for, the Office installation will go into trial mode and you will no longer be able to edit files. That means Access files will be read only.

Anyway, none of this is relevant to the OP's question. If you wish to continue with this, please start a new thread
 

Solo712

Registered User
Joined
Oct 19, 2012
Messages
749
Thanks Ranman256. The References are fine - none missing & all the same versions as works from Access 2003.

Thanks Gasman - my preferred option would have been to have left everything on Access 2003... (I much prefer the interface to that in 2010), but it's a corporate decision. Access 2003 is being taken off the network on 31st Dec 2019 and Access 2010 is going the following year.

I've been using the program on & off since 1991, so I'm going to be *really* stuck...

With an .mdb database, the code works when run from Access 2003, but fails when I open & run the same file/code from Access 2010.
Hi, I have a hunch that Ranman is on the right track and the issue has to do with mismatched references to Microsoft Excel XX.X Object Library. The problem is that sometimes the upgraded reference does not work. What you might want to try to do is to uncheck the reference, rerun the program and after you have been stopped, check it again and rerun to see if it works. If it doesn't - as it happened to me on occasion - you may want to switch to late binding to avoid Excel objects altogether.

Best,
Jiri
 

BUzzbomb23

New member
Joined
Dec 20, 2019
Messages
6
Can you directly open the same workbook through the Windows GUI? If it DOES open, do you get a yellow "not trusted" bar on the spreadsheet?
Workbook opens fine via Windows GUI. It also opens fine if I put the code in an .mdb file and run with Access 2003. If I open the same .mdb with Access 2010, the workbook opens, I then get the 1004 error...
 

BUzzbomb23

New member
Joined
Dec 20, 2019
Messages
6
What you might want to try to do is to uncheck the reference, rerun the program and after you have been stopped, check it again and rerun to see if it works. If it doesn't - as it happened to me on occasion - you may want to switch to late binding to avoid Excel objects altogether.
Thanks Jiri - Unchecking the Excel ref and using Late Binding, I still get 1004 error. Obviously with early binding, if I uncheck the Excel ref I get a compile error & then have to stop the process before recreating the Excel ref... and unsurprisingly once I've re-added the ref it fails with the 1004 error at the same point as before...
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom