opening an excel doc from VBA that has a Userform (1 Viewer)

[Jayce72]

I am trying to open an excel doc from a command button using vba.

I can open the excel file, but because the excel file has a userform on open, access is then locked and I can't even look at it and the excel userform doesn't work correctly.

Therefore, Im sure there's an extra step in access after opening the excel file to make it exclusive or something.

Apologies, if this is the wrong forum - but as this trans-ides both access and excel wasn't sure where

P.S: I have successfully open a standard excel file with no issues - it's just the userform causing the prob

Thanks in advance

 

[James Dudden]

What code are you using to open the Excel file?
Also, when you open it are you doing any kind of automation or just literally opening it?

 

[Jayce72]

Hi, This is the code i'm using. Ignore the first part as it's just copying the xls file into the temp folder. Therefore, the files named string is SDest and then I'm just opening it:

Regards

Jason

Dim SDest As String
Dim SSource As String
Dim DName As String
Dim Temp As String

DName = "new_file.xls"

Temp = Environ("temp") 'gets system variable and assign to Access variable
SDest = Temp & "\" & DName

SSource = "\\Folder\data\Apps\Documents\new_file.xls"

FileCopy SSource, SDest


Dim xlsApp As Excel.Application
Dim xlsBook As Excel.Workbook

Set xlsApp = CreateObject("Excel.Application")

Set xlsBook = Workbooks.Open(SDest)

 

[James Dudden]

So basically you open Excel as an object and assign it to the actual Excel file which 'locks' it in but you never release it. You could try to release it by adding these lines:

 Set Wbk = Nothing
 Set xlsApp = Nothing

I do question again though what you plan to do with the file once it's open. Are you planning to do any kind of automation or is opening the Excel file all you want to do.

If all you are doing is opening the Excel file then I would remove all your current code and just use something like this:

 Dim SDest As String
Dim SSource As String
Dim DName As String
Dim Temp As String

DName = "new_file.xls"

Temp = Environ("temp") 'gets system variable and assign to Access variable
SDest = Temp & "\" & DName

SSource = "\\Folder\data\Apps\Documents\new_file.xls"

FileCopy SSource, SDest
  
         Dim xpath As String, CallStr As String
        xpath = "c:\windows\explorer.exe"
        If Dir(SDest, vbDirectory) <> "" Then
            CallStr = xpath & " /n,/e," & Chr(34) & SDest& Chr(34)
            Call Shell(CallStr, 1)
        End IF

 

[Jayce72]

Thanks, I'll give your suggestions a go. Essentially, I open an Excel from access. Which is just a cmd button to open the file. The excel file auto displays a form with several fields to complete (in no way is it linked to access). Then they click a send button to auto send the completed form to an Email inbox and the Excel sheet auto closes.

All I'm using access for is to open and display the file.

I'll post after I've tried your suggestion

Regards

Jason

 

[Jayce72]

Hi

I tried:

Set Wbk = Nothing
Set xlsApp = Nothing

this didn't work.

I also tried your replacement code - but my office network does not allow direct access to "c:\windows\explorer.exe" therefore all I got was an error.

Any other ideas??

Thanks for your help though