Simple SAVEAS to another computer question

Topic

New Reply

I need to have my business quote form PRINT AND SAVE macro automatically save the new quote file to a different PC on the network. For years I’ve only had quotes being written on my main OFFICE PC. Now they will be getting written on both the OFFICE PC and the OFFICE2 PC but should all be saved in one location of the original OFFICE PC.

The following code has worked correctly for years by saving the file on the same PC that it was created on:

‘ THE FOLLOWING STATEMENT SAVES THE NEW QUOTE FILE
Range(“A1”).Select
Set Workbook = ActiveSheet
‘ Do
fName = Application.GetSaveAsFilename(“.xls”)
‘ Loop Until fName False
Workbook.SaveAs Filename:=fName

Now though if the quote has been created on OFFICE2 it needs to get saved on OFFICE instead. Running the RECORD macro feature I get the following:

ChDir “\OfficedMS Office DocumentsQuotes”
ActiveWorkbook.SaveAs Filename:= _
“\OfficedMS Office DocumentsQuotesTemp network.xls”, FileFormat:= _
xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:=False _
, CreateBackup:=False

This works except it doesn’t give me the option of entering a file name.

I’ve tried blending the 2 sets of code by inserting the fName descriptions but have not been successful. If the option to enter a filename works then I remain on the OFFICE2 PC for the save location. If the file is getting saved on the OFFICE PC where I want then its defaulting to “Temp network.xls” as the file name.

I’m close but just can’t seem to find the right way to do this.

As always your help is enormously appreciated.

BH

Reply | Quote

Replies

Hi BH,

The changes made in blue should resolve your issue. It will save to your local computer by prompting for a name then save to your networked computer using the new path but same name.

Code:

‘…..
Range(“A1”).Select
Set Workbook = ActiveSheet
fname = Application.GetSaveAsFilename(“.xls”)
Workbook.SaveAs Filename:=fname
[COLOR=”#0000FF”]s = Split(fname, “”)
fname = s(UBound(s))[/COLOR]
ChDir “\OfficedMS Office DocumentsQuotes”
ActiveWorkbook.SaveAs Filename:= _
“\OfficedMS Office DocumentsQuotes”[COLOR=”#0000FF”] & fname[/COLOR], FileFormat:= _
xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:=False _
, CreateBackup:=False
‘….
End Sub

HTH,
Maud

Note: this was untested

Reply | Quote

Hi BH,

The changes made in blue should resolve your issue. It will save to your local computer by prompting for a name then save to your networked computer using the new path but same name.

Code:

‘…..
Range(“A1”).Select
Set Workbook = ActiveSheet
fname = Application.GetSaveAsFilename(“.xls”)
Workbook.SaveAs Filename:=fname
[COLOR=#0000FF]s = Split(fname, “”)
fname = s(UBound(s))[/COLOR]
ChDir “\OfficedMS Office DocumentsQuotes”
ActiveWorkbook.SaveAs Filename:= _
“\OfficedMS Office DocumentsQuotes”[COLOR=#0000FF] & fname[/COLOR], FileFormat:= _
xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:=False _
, CreateBackup:=False
‘….
End Sub

HTH,
Maud

Note: this was untested

Thanks Maud,

What if I don’t need to save it on the local computer? Just delete the local computer top 4 lines of code? Or do I need those to predefine fName? I’d just try it but I’m at home now instead of at the office.

LIKE THIS BELOW?

Code:

‘…..

[COLOR=#0000FF]s = Split(fname, “”)
fname = s(UBound(s))[/COLOR]
ChDir “\OfficedMS Office DocumentsQuotes”
ActiveWorkbook.SaveAs Filename:= _
“\OfficedMS Office DocumentsQuotes”[COLOR=#0000FF] & fname[/COLOR], FileFormat:= _
xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:=False _
, CreateBackup:=False
‘….
End Sub

Thanks,
BH

Reply | Quote

Hi BH,

The changes made in blue should resolve your issue. It will save to your local computer by prompting for a name then save to your networked computer using the new path but same name.

Code:

‘…..
Range(“A1”).Select
Set Workbook = ActiveSheet
fname = Application.GetSaveAsFilename(“.xls”)
Workbook.SaveAs Filename:=fname
[COLOR=#0000FF]s = Split(fname, “”)
fname = s(UBound(s))[/COLOR]
ChDir “\OfficedMS Office DocumentsQuotes”
ActiveWorkbook.SaveAs Filename:= _
“\OfficedMS Office DocumentsQuotes”[COLOR=#0000FF] & fname[/COLOR], FileFormat:= _
xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:=False _
, CreateBackup:=False
‘….
End Sub

HTH,
Maud

Note: this was untested

Drats, didn’t work. As written above it prompts to save on the local HDD but then fails. It also fails without the first 4 lines of code. It’s saying something like S=split when I point at that line highlighted in yellow after the failure.

I don’t know if it matters but this is for Excel XP. I’ve maintained using it over the years because of the hassle it would be to deal with the macros on more recent versions.

Thanks,
BH

Reply | Quote

Correct!

Code:

‘…..
fname = ThisWorkbook.Name
ChDir “\OfficedMS Office DocumentsQuotes”
ActiveWorkbook.SaveAs Filename:= _
“\OfficedMS Office DocumentsQuotes” & fname, FileFormat:= _
xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:=False _
, CreateBackup:=False

HTH,
Maud

Note: again, untested but should work fine

Reply | Quote

BH,

in your original code, you were opening a save as dialogue box and prompting for a new name to save it to your local computer. If you need to still change the name, then why not just do a normal File > Save As > navigate to your network folder > Save?

Reply | Quote

BH,

in your original code, you were opening a save as dialogue box and prompting for a new name to save it to your local computer. If you need tostill change the name, then why not just do a normal File > Save As > navigate to your network folder > Save?

That’s what I was trying to do in the macro unsuccessfully. When I was adding the network path it was changing how it was working. I know a little more now about how it should be worded though so will try again.

Thanks,
BH

Reply | Quote

Glad I was able to help. You may want to keep in mind the you can pull the name for it to be saved directly from a cell or a form.

Change fname = ThisWorkbook.Name to

fname = Range(“B3”)

OR

fname= Userform1.textbox1.value

Reply | Quote

BH,

Please use one of the following codes to save your workbook to a network folder. Not sure if your intention is to prompt for a name.

Code:

Sub Save_File()
[COLOR=”#008000″]’SAVE FILE TO NETWORK FOLDER AS .XLS
‘————————————
‘SAVE WITH SAME NAME AS CURRENT WORKBOOK NAME[/COLOR]
    fname = ThisWorkbook.Name
    ActiveWorkbook.SaveAs Filename:=”\OfficedMS Office DocumentsQuotes” & fname, FileFormat:=xlExcel8

[COLOR=”#008000″]’————————————
‘SAVE WITH A PROMPTED NAME[/COLOR]
    fname = InputBox(“Enter file name with no extension”, “Save File as .xls”)
    ActiveWorkbook.SaveAs Filename:=”\OfficedMS Office DocumentsQuotes” & fname, FileFormat:=xlExcel8

[COLOR=”#008000″]’————————————
‘SAVE WITH NAME USING A CELL VALUE[/COLOR]
    fname = Range(“B3″) [COLOR=”#008000”]’ex: cell B3= “Yearly Quotes” without quotes[/COLOR]
    ActiveWorkbook.SaveAs Filename:=”\OfficedMS Office DocumentsQuotes” & fname, FileFormat:=xlExcel8

[COLOR=”#008000″]’————————————
‘SAVE WITH NAME USING VALUE FROM TEXTBOX ON A FORM[/COLOR]
    fname = Userform1.textbox1.Value
    ActiveWorkbook.SaveAs Filename:=”\OfficedMS Office DocumentsQuotes” & fname, FileFormat:=xlExcel8
End Sub

Reply | Quote

BH,

Please use one of the following codes to save your workbook to a network folder. Not sure if your intention is to prompt for a name.

Code:

Sub Save_File()
[COLOR=#008000]’SAVE FILE TO NETWORK FOLDER AS .XLS
‘————————————
‘SAVE WITH SAME NAME AS CURRENT WORKBOOK NAME[/COLOR]
    fname = ThisWorkbook.Name
    ActiveWorkbook.SaveAs Filename:=”\OfficedMS Office DocumentsQuotes” & fname, FileFormat:=xlExcel8

[COLOR=#008000]’————————————
‘SAVE WITH A PROMPTED NAME[/COLOR]
    fname = InputBox(“Enter file name with no extension”, “Save File as .xls”)
    ActiveWorkbook.SaveAs Filename:=”\OfficedMS Office DocumentsQuotes” & fname, FileFormat:=xlExcel8

[COLOR=#008000]’————————————
‘SAVE WITH NAME USING A CELL VALUE[/COLOR]
    fname = Range(“B3”) [COLOR=#008000]’ex: cell B3= “Yearly Quotes” without quotes[/COLOR]
    ActiveWorkbook.SaveAs Filename:=”\OfficedMS Office DocumentsQuotes” & fname, FileFormat:=xlExcel8

[COLOR=#008000]’————————————
‘SAVE WITH NAME USING VALUE FROM TEXTBOX ON A FORM[/COLOR]
    fname = Userform1.textbox1.Value
    ActiveWorkbook.SaveAs Filename:=”\OfficedMS Office DocumentsQuotes” & fname, FileFormat:=xlExcel8
End Sub

Maud,

Thanks. Will try in the morning.

I need to prompt for a name when the save is made so your 2nd suggestion should do the trick. Even better than before it would look like as now I think it is defaulting to the .xls file extension for us.

Thanks again,
BH

Reply | Quote

Maud,

That was close. A little editing and now this finally does the trick and brings it back to the original local folder to open the next file. Thanks for all your help.
BH

‘ THE FOLLOWING STATEMENT SAVES THE NEW QUOTE FILE TO A NETWORK LOCATION
Range(“A1”).Select
Set Workbook = ActiveSheet
fname = Application.GetSaveAsFilename(“\OfficedMS Office DocumentsQuotes” & fname & (“.xls”))
Workbook.SaveAs Filename:=fname

‘ This changes to the AAQuote Local Directory
ChDir “D:MS Office DocumentsQuotes”

Reply | Quote