Access 2010 - Win 7 problem opening Excel

[BUzzbomb23]

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

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]

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]

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]

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]

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?

 Set xlApp = new excel.application
.......
Set xlbook = xlApp.Workbooks.open("path")

 Set xlApp = CreateObject("Excel.Application")
.......
Set xlbook = xlApp.Workbooks.open("path")

 

[BUzzbomb23]

Thanks Adam. I get the same 1004 error at the "Set xlbook..." line.

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

 

[vba_php]

i have no idea what the problem is, however...

https://stackoverflow.com/questions...-giving-a-runtime-error-1004-the-disk-is-full

https://www.google.com/search?q=vba+open+workbook+1004+error

maybe you already did that though...

 

[eshai]

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]

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]

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]

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]

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]

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]

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]

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

 

[isladogs]

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

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]

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]

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]

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...