Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Open and run a macro in access runtime from excel

Hi,
This is Microsoft XP and I am trying to use Excel to open a runtime access database and run a macro.  I found this code but I'm not sure where I put my access commands.

Sub OpenRunTimePat()
Dim objAccess As Object
  Dim accpath As String, dbpath As String
  On Error Resume Next
  dbpath = "s:\rcsxp\rcsxp.mdb"
  Set objAccess = GetObject(dbpath)
  If Err <> 0 Then
    If Dir(dbpath) = "" Then 'dbpath is not valid
      MsgBox "Couldn't find database."
      Exit Sub
    Else  'The full version of Microsoft Access is not installed.
'      accpath = "C:\Program Files\Common Files\Microsoft Shared" & _
'           "\Microsoft Access Runtime\MSAccess.exe"
      accpath = "Y:\Program Files\Microsoft Office" & _
           "\Office10\MSAccess.exe"
      If Dir(accpath) = "" Then
         MsgBox "Couldn't find Microsoft Access."
         Exit Sub
      Else
         Shell pathname:=accpath & " " & Chr(34) & dbpath & Chr(34), _
           windowstyle:=1
         Do 'Wait for shelled process to finish
           Err = 0
           Set objAccess = GetObject(dbpath)
         Loop While Err <> 0
      End If
    End If
  End If
Set objAccess = Nothing
End Sub

I am using "Y"   because we have Citrix.  The users have Excel and Word but no Access.  I need to Open S:\RCS\Rcs.mdb and run the macro "Import Pat"
Does anyone know if this is possible and how to do it??
Thanks
Lisa
0
LisaPrice
Asked:
LisaPrice
  • 6
  • 6
1 Solution
 
shanesuebsahakarnCommented:
Hmm, don't recall if this works with the runtime, but use the /x command line parameter to execute a macro when opening Access.

Before the shell line, add:
pathname=pathname & " /x """Import Pat""""

I *think* that will work, but I don't use macros at all.
0
 
LisaPriceAuthor Commented:
This is how I wrote it and it errors out.
Shell pathname:=accpath  & Chr(34) & dbpath & Chr(34)& " /x """Import Pat"""", _
           windowstyle:=1
and the reason I am using a macro is because it runs a queries and transfers text to a table.  IF I was to use a function how could i write it?

Lisa
0
 
shanesuebsahakarnCommented:
There's are a couple of extra "s in there, my fault - try:
Shell pathname:=accpath  & Chr(34) & dbpath & Chr(34) & " /x ""Import Pat""", _
           windowstyle:=1

You can run queries using DoCmd.OpenQuery, and DoCmd.TransferText to import/export text - these are the exact equivalents of their macro actions. In fact, if you click on a macro and go Tools->Macro->Convert Macros to Visual Basic, Access can do it for you, but try the above first.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LisaPriceAuthor Commented:
I didn't get any errors but it didn't do what it's suppose to do.  I opened the database and no records were imported.  Its like it doesn't run the Import Pat macro at all.
Lisa
0
 
shanesuebsahakarnCommented:
Hmm, I'm not sure if the spaces have any effect. Can you rename Import Pat to say Import_Pat?
0
 
LisaPriceAuthor Commented:
I watched my task bar and it isn't even opening access.  Windowstyle:= 1 should show access.  The path I am using is
      accpath = "E:\Program Files\Microsoft Office" & _
           "\Office10\MSAccess.exe"

Thats where our network person said office is installed for citrix (I have a regular desktop and citrix) and i looked and its there.  The original path from the code I found was C:\ProgramFiles\CommonFiles\MicrosoftShared\
Microsoft Access Runtime\msaccess.ext
we don't have any folder Microsoft Access Runtime except in my Microsoft Access Developer which the users don't have.  I'm not sure if its the path or the shell line?
Lisa
0
 
shanesuebsahakarnCommented:
Hmm, IIRC Shell (at least the Access version) needs DOS pathnames, not Win long filenames. I could be wrong about that, but try changing your paths into DOS8.3 format.
0
 
LisaPriceAuthor Commented:
Huh?? I don't understand/
I'm not a programmer, just try because they tell me to and read a lot of help.
0
 
shanesuebsahakarnCommented:
Hmm, ok, well in DOS, filenames can be a maximum of 8 chars long. To convert a Windows long folder/filename to a DOS format filename, you take the first 6 (if it is longer than 8) letters and add a ~<number>. The number depends on how many others are have the same first six letters, but it's usually 1. So:

E:\Program Files\Microsoft Office\Office10\MSACCESS.EXE
becomes:
E:\PROGRA~1\MICROS~1\OFFICE10\MSACCESS.EXE

for example. You'll also need toconvert your dbpath string as well.
0
 
LisaPriceAuthor Commented:
That didn't work either.  It must be in the path.  I don't have any runtime folder in the common files folder anywhere , would that have anything to do with it.  
Lisa
0
 
shanesuebsahakarnCommented:
It shouldn't do - in your code above, you have those lines commented out. Is that correct?

Do your Citrix users all have access to the full version of Access installed on the Y: drive?
0
 
LisaPriceAuthor Commented:
Finally, after much persuading, and since I can't get any of the above to work (I'm running out of time), I have managed to get the two users a full version of access so I can just use the acc.OpenCurrentdatabase command along with acc.docmd to run the queries and file transfer.  I do thank you for all your help and maybe if I had more time we could have gotten it to work.  Thanks
Lisa
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now