PDA

View Full Version : Solved: Excel 2003 - Is it complicated to save to a shared network drive?



frank_m
10-26-2010, 12:58 AM
My question is below this code:
Private Sub CommandButton2_Click()
Dim wb As Workbook
Dim fileSaveName As String
Dim InitFileName As String
Dim strDate As String

strDate = Format(Now, "dd-mmm-yy h-mm-ss")

InitFileName = "C:\Users\Owner\Desktop\" & Range("I4").Value & "_" & strDate

Sheets(Array("Packing Slip", "C of C", "Invoice", "P Slip")).Copy
Set wb = ActiveWorkbook

fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitFileName, _
filefilter:="Excel files , *.xls")

If fileSaveName <> "False" Then
wb.SaveAs fileSaveName
wb.Close
Else
wb.Close False
Exit Sub
End If

End Sub The code I put together above works fine to save four sheets as a new workbook.

I'm wondering if it will work if used with a shared network path

Changing this:
InitFileName = "C:\Users\Owner\Desktop\" & Range("I4").Value & "_" & strDate To this:
InitFileName = "\\CompanyShare\folder1\" & Range("I4").Value & "_" & strDate I only go into the factory a couple times a month so I have no way to test it. - I am hoping I do not need to use a big long API routine to get the save to path into my routine.

Thanks in advance for some insight.

Jan Karel Pieterse
10-26-2010, 01:13 AM
If the folder is correct and the user has read/write/delete access rights, then it should work just fine.

frank_m
10-26-2010, 01:30 AM
Thanks Jan,

The user will have read/write/delete access rights, that I am sure of.

If it doesn't work, I will be ok even if I skip navigating in code to folder1 and save directly to \\CompanyShare\ and I found this short API call that looks like it will get me there
Declare Function SetCurrentDirectoryA Lib "KERNEL32" (ByVal lpPathName As String) As Long

SetCurrentDirectoryA "\\CompanyShare\"

InitFileName = Range("I4").Value & "_" & strDate
API found at: http://www.eggheadcafe.com/community/aspnet/66/10145398/vba-save-file-into-network-drive.aspx

I will mark this as solved and post back in a couple weeks when I get a chance to test it.

Thanks again, I really appreciate you letting me tap into your brain and experience. :friends: