Saving Doc in 2 places

MrCrud · #1 (**permalink**) 01-22-04, 12:51

Hi,

I need to be able to save a worksheet in two places at the same time. The first place is on the HD of the computer in question, and the other place is on the hd of another computer over the network.

How would i create a macro to do this? I havent worked with excel macros so far, but i have experience with access.

Thanks!

Smitty · #2 (**permalink**) 01-22-04, 13:28

Probably the fastest way would be to simply record a macro, saving to one location first, then the next. You can then assign the macro to a button or put it in the ThisWorkbook module in a Private Sub Workbook_BeforeClose(Cancel As Boolean) event.

Here's a pared down sample of what you'd get.

Sub SaveMe2()

With ActiveWorkbook
.SaveAs Filename:="H:\Network Path\Filename.xls"
.SaveAs Filename:="C:\Local Path\Filename.xls"
End With

End Sub

Hope that helps,

Smitty

MrCrud · #3 (**permalink**) 01-22-04, 16:12

Thanks for the reply. This is very helpful info, however, it is not quite what i want. The workbook will not be closed. I simply need to do a ' Save As '. Ideally, after that, it would open a new workbook, from the same template, and after that, close the original workbook.

Also, will the method you mention ask the user under what name they want to save the workbook? If not, is there a way to make a specific cell in the workbook become the name if the files?

THanks!

Smitty · #4 (**permalink**) 01-22-04, 16:46

The SaveMe2 macro I posted will only perform a Save As; it won't close the workbook.

It will not prompt for a filename or save as a range name, but this will:

Sub SaveMe2()

Dim fname As String
' Save as Range Name
fname = Sheets("Sheet1").Range("A1")

' Save as user Input
' fname = InputBox("Please enter the target Filename", "Filename Entry")

With ActiveWorkbook
.SaveAs Filename:="H:\Network Path\" & fname & ".xls"
.SaveAs Filename:="C:\Local Path\" & fname & ".xls"
End With

End Sub

As for opening a new file, you can record that as well. To close the ActiveWorkbook, put ActiveWorkbook.Close True (if you want to save it) False (if you don't want it saved) at the end of your code.

Hope that hgelps,

Smitty

MrCrud · #5 (**permalink**) 01-23-04, 13:46

Hi,

I tried the code you wrote, changed the network paths properly, but when i run it, i get an error saying:

Subscript out of range

What causes this?

thanks!

Smitty · #6 (**permalink**) 01-23-04, 13:53

Can you post your code and indicate the line that it's bombing on?

MrCrud · #7 (**permalink**) 01-23-04, 13:56

Nevermind, i managed to fix it. I needed to specify the sheet's name and it's range ( totally forgot about that!!! )

the only thing i would like to do, is modify the image of the button i've just created on the toolbar .....I'm not sure i want to keep the Happy Face excel put there as a default...

Any ideas?

and while we're at it, what do i need to add to the code so that it prints out the current sheet in 3 copies? and then closes the sheet, and opens a new one?

thanks!

Smitty · #8 (**permalink**) 01-23-04, 14:08

Glad you got it worked out. I kinda figurted it was something like that, but without actually seeing the code, you can't be too sure.

As for the image, goto Tools-->Customize-->Commands-->Macros, then right-click the image on the Toolbar and select Edit Image.

Smitty

MrCrud · #9 (**permalink**) 01-23-04, 14:32

i managed to change the image on my own, the help file for this isnt spectacular....

is it possible to have it print the sheet in 3 copies, close the sheet, and open a new one from the original ( before any data is entered )??

This is pretty much the only thing left i would like.

So far, i know i have to use something like this:

ActiveSheet.PrintOut( , ,3, , Name of printer)

but i'm not sure what i should enter as the name of the printer. Since this is a network printer, do i have to enter the full path to the printer?

Smitty · #10 (**permalink**) 01-23-04, 14:42

Sure.

For the printing portion, the best way to get the correct code and network path is to record it, then add the generated code to your existing code.

For the workbook close portion, if you want to close the ActiveWorkbook and then open a new one, you'll need to do it in reverse order.

Open a new workbook, activate the old one, then close it.

Smitty

MrCrud · #11 (**permalink**) 01-23-04, 14:45

For the printing, how do i record the network path? I already have the proper net work path, cant i just write that in?

if you could write some sample code, that would help a lot. Once i have the basic structure, it's much easier for me to change what needs to be.

So far, this is the code i have:

Sub SaveMe2()

Dim fname As String

' Save as Range Name
fname = Sheets("Invoice").Range("N57")

' Save as user Input
fname = InputBox("Please enter the target Filename", "Filename Entry")

With ActiveWorkbook
.SaveAs Filename:="\\ADMIN\My Documents\Comptabilité\FactureClients\" & fname & ".xls"
.SaveAs Filename:="C:\Documents and Settings\Mr.Crud\My Documents\E-Comm\Audio Occasion\Factures Clients" & fname & ".xls"
End With

Dim Copies As Variant
Dim ActivePrinter As Variant

ActiveSheet.PrintOut( , ,3, ,"\\SERVICEPLUS\HL1440")

End Sub

but it doesnt work, i get a compile error saying i'm missing a ' = ' and brings the cursor to the end of my print line...

Thanks again for the help, it's very appreciated!

Cheers!

Smitty · #12 (**permalink**) 01-23-04, 14:53

To get the exact network path, record a new macro, goto File-->Print and select the Network printer of choice. Give Excel time to map the path, then Cancel & stop recording. Note that after recording you'll need to set Excel back to the default printer.

Here's a sample:

Code:

Sub NetworkPrint_Close()

    Dim fname As String
    fname = ActiveWorkbook.Name
    
    Application.ActivePrinter = "\\SDISS01\sdSLi03 on Ne02:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=3, Collate:=True
    
    Workbooks.Open Filename:= _
        "C:\File Path\File Name.xls"
    
    fname.Close ' False or True depending on if you want to save or not
    
End Sub

Smitty

MrCrud · #13 (**permalink**) 01-23-04, 15:04

I suppose that NeO2 is the name of the computer on which the printer is installed??

Since i want all the the steps to be executed from the single click of my button, would something like this work?

Sub SaveMe2()

Dim fname As String

' Save as Range Name
fname = Sheets("Invoice").Range("N57")

' Save as user Input
fname = InputBox("Please enter the target Filename", "Filename Entry")

With ActiveWorkbook
.SaveAs Filename:="\\ADMIN\My Documents\Comptabilité\FactureClients\" & fname & ".xls"
.SaveAs Filename:="C:\Documents and Settings\Mr.Crud\My Documents\E-Comm\Audio Occasion\Factures Clients" & fname & ".xls"
End With

Dim fname As String
fname = ActiveWorkbook.Name

Application.ActivePrinter = "\\SERVICEPLUS\HL1440 on SERVICEPLUS:"
ActiveWindow.SelectedSheets.PrintOut Copies:=3, Collate:=True

Workbooks.Open Filename:= _
"C:\File Path\Invoice.xls"

fname.Close ' False or True depending on if you want to save or not

End Sub

Smitty · #14 (**permalink**) 01-23-04, 15:31

How's this:

Code:

Sub SaveMe2()

    Dim fname1 As String
    Dim fname2 As String
    Dim fname3 As String

    fname1 = ActiveWorkbook.Name
    
    ' Save as Range Name
    fname2 = Sheets("Invoice").Range("N57")
    
    ' Save as User Input
    fname3 = InputBox("Please enter the target Filename", "Filename Entry")
    
    With ActiveWorkbook
        .SaveAs Filename:= _
            "\\ADMIN\My Documents\Comptabilité\FactureClients\" _
            & fname2 & ".xls"
        .SaveAs Filename:= _
            "C:\Documents and Settings\Mr.Crud\My Documents\E-Comm\Audio Occasion\Factures Clients" _
            & fname3 & ".xls"
    End With
    
    Application.ActivePrinter = "\\SERVICEPLUS\HL1440 on SERVICEPLUS:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=3, Collate:=True
        
    Workbooks.Open Filename:="C:\File Path\Invoice.xls"
    
    fname1.Close ' False or True depending on if you want to save or not i.e.
    '   fname1.Close True
    '   Will save
    '   fname1.Close False
    '   Will not
        
End Sub

Note that the way you have it set, the workbook will Save As Range Name, Save As User Input and save 2 copies.

Smitty

MrCrud · #15 (**permalink**) 01-23-04, 15:59

Your code saves the files properly, but i cant get it to print and it doesnt close the current workbook after it has opened the next one.

Also, after i run the macro and i go back to the code it has changed to this:

Sub SaveMe2()

Dim fname As String

' Save as Range Name
fname = Sheets("Invoice").Range("N57")

' Save as user Input
fname = InputBox("Please enter the target Filename", "Filename Entry")

With ActiveWorkbook
.SaveAs Filename:="\\ADMIN\My Documents\Comptabilité\FactureClients\" & fname & ".xls"
.SaveAs Filename:="C:\Documents and Settings\Mr.Crud\My Documents\E-Comm\Audio Occasion\Factures Clients" & fname & ".xls"
End With

' Dim fname As String
' fname = ActiveWorkbook.Name

' Application.ActivePrinter = "\\SERVICEPLUS\HL1440 on Ne02:"
' ActiveWindow.SelectedSheets.PrintOut Copies:=3, Collate:=True

' Workbooks.Open Filename:= _
"C:\File Path\File Name.xls"

' fname.Close ' False or True depending on if you want to save or not

End Sub

As you can it, it put a bunch of code as comments???

Internet Services

Web Development

Digital Marketing

Consumer Tech