Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Nov 2003
    Posts
    150

    Unanswered: Saving Doc in 2 places

    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!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  2. #2
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    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

  3. #3
    Join Date
    Nov 2003
    Posts
    150
    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!
    Last edited by MrCrud; 01-22-04 at 17:19.
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  4. #4
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    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

  5. #5
    Join Date
    Nov 2003
    Posts
    150
    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!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  6. #6
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Can you post your code and indicate the line that it's bombing on?

  7. #7
    Join Date
    Nov 2003
    Posts
    150
    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!
    Last edited by MrCrud; 01-23-04 at 15:16.
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  8. #8
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    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

  9. #9
    Join Date
    Nov 2003
    Posts
    150
    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?
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  10. #10
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    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

  11. #11
    Join Date
    Nov 2003
    Posts
    150
    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!
    Last edited by MrCrud; 01-23-04 at 15:47.
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  12. #12
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    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

  13. #13
    Join Date
    Nov 2003
    Posts
    150
    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
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  14. #14
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    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

  15. #15
    Join Date
    Nov 2003
    Posts
    150
    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???
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •