Hi there, I am trying to open an access database from excel by clicking a button. I have assigned the following script and modified it for my own use as according to the microsoft kb.
The problem I have is that although this does not debug, it does not do what I want it to.
I want this to launch Access, then launch the file FCRs.mdb, then launch the macro ImportData.
What it actually IS doing is the following:
ERROR 1:
Remote Data not accessible.
To access this data Excel needs to start another application. Some legitimate applications on your computer could be used maliciously to spread viruses or damage your computer. Only click yes id you trust the source of this workbook and you want to let the workbook start the application.
Start application 'MSACCESS.EXE'?
Even though access has by now already started anyway, I then click yes.
ERROR 2:
Microsoft Office Access can't find the macro 'DataInput.'
The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under.
I then click ok, knowing the macro exists, but soon realising the application has opened, and the macro is trying to open, but the file isnt opening at all.
ERROR 3:
The command line you used to start Microsoft Office Access contains an option that Microsoft Office Access doesn't recognize.
Exit and restart Microsoft Office Access using valid command line options.
I click ok, and get ERROR 3 again.... and again.
ERROR 4:
Microsoft Office Access cant find the database file 'T:\TSD.mdb'
Make sure you enterred the correct path and filename.
So now at the end of the cascading messages, i eventually find what seems to be the problem. The macro couldnt be openned because the file wasnt open, because it was looking for T:\TSD.mdb, when it is supposed to be looking for T:\TSD - UK\Projects\Steve's Projects\T3\FCRs.mdb.
My big guess is that this has something to do with spaces in names, because if i change it to T:\TSD-UK\Projects\Steve'sProjects\T3\FCRs.mdb, i get the same problem, but with the full path name, so how do i resolve this problem?
I have the following refferences attached:
Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Access 11.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Please help
Private Sub CommandButton2_Click()
'Opens Microsoft Access and the file nwind.mdb
Shell ("c:\Program Files\Microsoft Office\OFFICE11\MSACCESS.exe T:\TSD - UK\Projects\Steve's Projects\T3\FCRs.mdb")
'Initiates a DDE channel to Microsoft Access
Chan = DDEInitiate("MSACCESS", "system")
'Activates Microsoft Access
Application.ActivateMicrosoftApp xlMicrosoftAccess
'Runs the macro "Sample AutoExec" from the NWIND.MDB file
Application.DDEExecute Chan, "ImportData"
'Terminates the DDE channel
Application.DDETerminate Chan
End Sub
The problem I have is that although this does not debug, it does not do what I want it to.
I want this to launch Access, then launch the file FCRs.mdb, then launch the macro ImportData.
What it actually IS doing is the following:
ERROR 1:
Remote Data not accessible.
To access this data Excel needs to start another application. Some legitimate applications on your computer could be used maliciously to spread viruses or damage your computer. Only click yes id you trust the source of this workbook and you want to let the workbook start the application.
Start application 'MSACCESS.EXE'?
Even though access has by now already started anyway, I then click yes.
ERROR 2:
Microsoft Office Access can't find the macro 'DataInput.'
The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under.
I then click ok, knowing the macro exists, but soon realising the application has opened, and the macro is trying to open, but the file isnt opening at all.
ERROR 3:
The command line you used to start Microsoft Office Access contains an option that Microsoft Office Access doesn't recognize.
Exit and restart Microsoft Office Access using valid command line options.
I click ok, and get ERROR 3 again.... and again.
ERROR 4:
Microsoft Office Access cant find the database file 'T:\TSD.mdb'
Make sure you enterred the correct path and filename.
So now at the end of the cascading messages, i eventually find what seems to be the problem. The macro couldnt be openned because the file wasnt open, because it was looking for T:\TSD.mdb, when it is supposed to be looking for T:\TSD - UK\Projects\Steve's Projects\T3\FCRs.mdb.
My big guess is that this has something to do with spaces in names, because if i change it to T:\TSD-UK\Projects\Steve'sProjects\T3\FCRs.mdb, i get the same problem, but with the full path name, so how do i resolve this problem?
I have the following refferences attached:
Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Access 11.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Please help