1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Open Existing and Save As through currently Open Document

Discussion in 'VBA Macros' started by jsabin, Aug 5, 2018.

  1. jsabin

    jsabin New Member

    Messages:
    14
    Hoping you guys can help. I have an existing Open document where I will assign a macro to a button. I want that macro to open another file and save that file to a new name. Here's the code I have so far but it doesn't seem to work.

    Code (vb):

    Sub Test()
      Workbooks.Open Filename:=ThisWorkbook.Path & "\TEMPLATE.xlsm"
      Dim varCellvalue As String
      varCellvalue = Range("AM12").Value
      Windows("TEMPLATE.xlsm").Activate
      ActiveWorkbook.SaveAs = ThisWorkbook.Path & "\SAVED PROJECTS\" & varCellvalue & ".xlsm"
    End Sub
     

    ! Use CODE TAGS !
    Last edited by a moderator: Aug 5, 2018
  2. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    So what seems the issue ?
  3. jsabin

    jsabin New Member

    Messages:
    14
    I'm Getting a compile Error: "Expected Function or Variable" which highlights the ".SaveAs =" in the code.
  4. Marc L

    Marc L Excel Ninja

    Messages:
    4,253

    So just reading the VBA inner help for SaveAs method
    you must see the equal sign is not necessary ! …​
  5. jsabin

    jsabin New Member

    Messages:
    14
    That helps. I think I'm getting closer. What I'm getting now is a "Method 'Save As' of "object'_Workbook' failed" error. The Debugger takes me to this line.

    [ActiveWorkbook.SaveAs ThisWorkbook.Path & "\SAVED PROJECTS\" & varCellvalue & ".xlsm"]
  6. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Check :

    ¤ if each folder already exists

    ¤ what contains the cell / variable used in the path ?
  7. jsabin

    jsabin New Member

    Messages:
    14
    As a test, I changed the code to this below and it worked flawlessly. The problem is the name of the file does need to be dynamic, based on user input.

    [Sub Test()
    Workbooks.Open Filename:=ThisWorkbook.Path & "\DESIGN-PAD\DESIGN-PAD.xlsm"
    Windows("DESIGN-PAD.xlsm").Activate
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\DESIGN-PAD\SAVED PROJECTS\TEST.xlsm"
    End Sub]
  8. jsabin

    jsabin New Member

    Messages:
    14
    Update:
    I found the problem. Now I just need to figure out how to fix it. The code is referencing cell AM12 of the file that the code is opening and saving. It needs to reference AM12 on the file that contains the macro which is titled "USER INTERFACE" on sheet also titled "USER INTERFACE"
  9. Marc L

    Marc L Excel Ninja

    Messages:
    4,253


    Like Range("'USER INTERFACE'!AM12").Text …​
  10. jsabin

    jsabin New Member

    Messages:
    14
    Thank you very much Marc for your help! Here is the end code if you're interested.
    Thanks again!

    [Sub Test()
    Workbooks.Open Filename:=ThisWorkbook.Path & "\DESIGN-PAD\DESIGN-PAD.xlsm"
    Dim varCellvalue As String
    varCellvalue = Workbooks("USER INTERFACE.xlsm").Sheets("USER INTERFACE").Range("AM12").Value
    Windows("DESIGN-PAD.xlsm").Activate
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\DESIGN-PAD\SAVED PROJECTS\" & varCellvalue & ".xlsm"
    End Sub]
  11. jsabin

    jsabin New Member

    Messages:
    14
    on a side note, I just noticed the moderator edit requesting to use code tags. I thought that's what I was doing by enclosing the code with []. Do you know what I'm doing wrong?
  12. Marc L

    Marc L Excel Ninja

    Messages:
    4,253

    As it's permanently written in the Tip frame below
    or just use the code icon …​

Share This Page