#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    4
    Rep Power
    0

    Is there any way then to SaveAs without taking the focus off the original spreadsheet


    Is there any way then to SaveAs without taking the focus off the original spreadsheet?

    I want to SAVE a copy of the workbook(XLSM) as XML in the background.
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,443
    Rep Power
    4539
    You'll probably need to write some vba code.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by Doug G
    You'll probably need to write some vba code.
    Funny.

    Yes I hoping to get some help with that part.

    I got this far but the macro seems to stop executing after the SAVEAS the next two line CLOSE and OPEN the original don't happen.

    Sub test()
    reopen = ActiveWorkbook.Name
    ActiveWorkbook.SaveAs Filename:="C:\Users\me\Documents\xmlbook2.xml", FileFormat:=xlXMLSpreadsheet
    ActiveWorkbook.Close
    Workbooks.Open reopen
    End Sub


    I was wondering if this should be done using "external" script perhaps WSH assuming I can't overcome the probelm above.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by datanull
    Funny.

    Yes I hoping to get some help with that part.

    I got this far but the macro seems to stop executing after the SAVEAS the next two line CLOSE and OPEN the original don't happen.

    Sub test()
    reopen = ActiveWorkbook.Name
    ActiveWorkbook.SaveAs Filename:="C:\Users\me\Documents\xmlbook2.xml", FileFormat:=xlXMLSpreadsheet
    ActiveWorkbook.Close
    Workbooks.Open reopen
    End Sub


    I was wondering if this should be done using "external" script perhaps WSH assuming I can't overcome the probelm above.

    I made a VBSCRIPT program that works as I wanted. Now all I have to do is figure out how to make a button in excel that calls this program.

    Const xlXMLSpreadsheet = 46
    Dim sArray

    Set objExcel = GetObject(,"Excel.Application")
    objExcel.Visible = True
    Set objWorkbook = objExcel.ActiveWorkbook

    reopen = objWorkbook.Name
    path = objWorkbook.Path
    sArray = Split(reopen,".")


    objWorkbook.Save
    objWorkbook.SaveAs path & "\" & sArray(0),xlXMLSpreadsheet

    objExcel.ActiveWorkbook.Close
    objExcel.Workbooks.Open path & "\" & reopen
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    18
    Rep Power
    0

    name v fullname


    Your problem is that activeworkbook.name is just the name of the file, you need path and name to open a file, activeworkbook.fullname is the name and path

    All your code needs is an extra "full" and it will work fine

    Just a thought, the way you have it your code will reopen the original file as it was before any edits, if you want it to be as it is after any edits you have done use:

    Sub test()

    justTheName= ActiveWorkbook.Name
    Nameaandthepath = ActiveWorkbook.FullName

    ActiveWorkbook.Sheets.Copy
    ActiveWorkbook.SaveAs Nameaandthepath & "xx"
    ActiveWorkbook.Close



    End Sub
    Last edited by GGriggs; February 2nd, 2013 at 10:40 AM. Reason: typo

IMN logo majestic logo threadwatch logo seochat tools logo