Needing a macro to save in another workbook
Needing a macro to save in another workbook
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Needing a macro to save in another workbook

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I sent this before but received an error so I am not sure it went through.

    I hope I am not asking a simple question thats been responded to numerous times but a quick search did not show anything like I needed to know for my problem.

    Example:
    I have workbook "ABC" in a folder "123". I need a macro in workbook "ABC" to "save as" the open workbook into folder "456" with the same file name as the open workbook. I also need a notation made in a cell of each of the workbooks showing the workbook has been copied after the save as command has completed. I have gotten most of this to work with the excemption of being able to save the new workbook in a different folder with the same file name as the open workbook. Workbook "ABC" will have various saved file names with the same macro copied into them, so the save as macro will need to be able to refer to the file name or a cell within the file to use as the name for the new file in the second folder.


    Anybody have any suggestions or need clarification of what I am talking about?

    Thanks

    [ This Message was edited by: Saa62 on 2002-03-21 16:48 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried but cannot exactly get a grip here.
    You want Workbook 1 from folder A to run its own macro and save itself as Workbook 2 in folder B?

    How's that for crafty repetion?

    I think you are saying that you want workbook 1 to keep a running list of all the saved as workbook names in folder B which have the same name from what I can gather.
    We know that two files with the same extention cannot live together in the same folder...

    Whatever you are trying to do, it can most likely be done with VB.
    However, I really need to know exactly what you intend to do???????

    What are the cell references for as far as which macro is concerned?????

    Try again please!


  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Let me go back a little farther to explain where I ended up, to hopfully clarify what I am trying to do.

    I have an Access database with a macro that will pull selected pieces of data from a record. Then copy this data into an excel workbook into particular cells on the first sheet in the excel workbook. That information is then linked to several other sheets within the workbook to be placed in the correct cells to fill out the various forms used by my company. The macro will then do a "save as" and name the file by the log number from the Access record and put it into another folder. This last "Save As" is Workbook 1 in folder A that you used in your description. This mean that workbook 1 will take on various names depending on what log number the data was pulled from in Access. All of the above in done from Access.

    This much of the program has worked well. The information in the workbook is then copied off and sent to the various suppliers. We are now wanting to email the information to the various suppliers but before it can be sent out it must be reviewed by another department within our company.

    This is why I need to be able to send a copy of workbook 1 in folder A to folder B with the same file name. Folder B would be a folder that is available to the necessary personel to review. The notation I need would be something like "Sent to TL" in workbook 1 in folder A to alert other users that the file had already been sent.

    Due to large number of users that are not overly trained in moving files it is necessary to simplify it as much as possible. I am hoping that a macro button within the workbook would be easy enough to get the files where they need to go.

    I hope that clarifies what I am wanting do. Any suggestions?

    Thanks

    [ This Message was edited by: Saa62 on 2002-03-23 06:24 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok. I think I understand.
    You simply need the code to save the workbook in another folder while retaining its name.

    I have never done that before, but even if you cannot save a workbook under the same name to a different location, it can be saved with a different name, and then renamed. All this can be done behind the scenes.

    I'll see what I can draw up...


  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I hope this is what you are looking for.
    Make sure the path is edited and correct or errors will keep your mini macro from working correctly.

    If this is not your answer, keep posting with as much info as you can give. There are some extremely capable people here. Someone will surely be able to help you.

    Tom

    P.S. Create the button you wanted and name it cmdSaveCopy, in design mode double-click to bring up the code window. Replace the buttons cmdSaveCopy_Click event with the code below and run after you have edited.
    I can't get rid of the double There should only be one forward slash in each position.

    Private Sub cmdSaveCopy_Click()

    Dim CompletPathToFolder_B As String

    On Error GoTo Oops

    CompletPathToFolder_B = "C:WIN95 DesktopFolder_B" & ThisWorkbook.Name

    ThisWorkbook.SaveCopyAs CompletPathToFolder_B

    'notation
    Sheet1.Range("A1").Value = "Last sent to TL on " & _
    format(now,"MMMM DDD, YYYY HH:MM AMPM")


    Exit Sub
    Oops:
    MsgBox "Error - Copy of " & ThisWorkbook.Name & " may not have been saved!"
    End Sub


    How do you get rid of these double forward slashes????

    [ This Message was edited by: TsTom on 2002-03-23 07:42 ]

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Prelimenary testing is showing this will solve my problem. I won't know for sure until I start incorporating it with rest of the program but this looks like it will do the trick.

    Thanks

    PS. The date and time stamp is a great idea on the notation. I will be using that.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com