Code to open an excel file in a Network "shared" folder...

[michaeljohannes]

Hello,

Can someone give me a hand with a file path to a computer on a basic network shared folder?

This is what I have so far:

Private Sub cmdGenCheckList_Click()

   
    Dim objXLApp As Object
    Dim objXLBook As Object
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open("\\comp_sharedfolder\JOB_CHECKLIST.xltm")
    objXLApp.Application.Visible = True

    objXLBook.ActiveSheet.Range("D3") = Me.JobOrderNumber
    objXLBook.ActiveSheet.Range("D2") = Me.LastName
    
    
    
    
End Sub

If the file JOB_CHECKLIST.xltm was stored locally on my machine, the path would be "c:\comp_sharedfolder\" but because it's stored on a different machine, the path is something different. The computer name is called "CONF" and (for the sake of discussion) the folder is described above.

Any suggestions?

Thanks!
Mike

 

[boblarson]

Just use UNC:

\\YourServerName\Share\Folder\FileName.xls

But, you have to remember to start from the very root server name and include the share.

 

[michaeljohannes]

Thanks, Bob. That worked.

Is there a way I can set Excel to force the user to save the new file in that same directory? What's happening now is when I press save, the default save location is on my local machine, rather than the server. Obviously, I want these excel spreadsheets to be stored in the server folder.

Thanks for your help!
Mike

 

[boblarson]

At the end of your code, when done put this:

objXLBook.SaveAs

And then use the parameters to set it.

 

[michaeljohannes]

I've added this line to the code:

objXLBook.SaveAs ("\\CONF\sharedfolder\checklists\" & Me.JobOrderNumber & ".xlsm")

but am getting a VB error...

It's something to do with Macro Enabled workbooks in 2007. My template checklist has checkboxes in some of the fields which requires it to be macro-enabled. Althought the .xlsm file is the 2007 file type for Macro-Enabled workbooks, Excel is having issues with it...

Any ideas?

Mike

 

[boblarson]

I haven't tried saving documents through code in 2007 yet. That's something I'll have to play with and get back to you, if you don't figure it out first or find the answer elsewhere first.

 

[michaeljohannes]

I should correct what I said... It's not really a VB error, it's an excel error. The "SaveAs" code is somehow trying to save the file automatically to a macro-free workbook instead of a macro-enabled workbook. If this was a normal excel file without macros, I think it would work fine. I will test that...

 

[boblarson]

still, there should be a way...we just have to find it.