Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro Problem - Saving Open spreadsheet (Excel 97)

    I have two spreadsheets. Mastertest is the spreadsheet without any data and just the macros. On the test.xls spreadsheet I run a macro to import data from access and create the reports. I Open Test.xls and then to make sure that it is empty (without any data) I start up this macro.

    In Test.xls/macro1 I ask it to open Mastertest.xls and save it as Test.xls. When I run this in a manual manner it tells me that test.xls is already there and if I want to save it anyway. I answer Yes. And the Test.xls that is open is now displaying the screen as it appears in Mastertest.xls. When I run this as a macro is does the first part and aborts when it gets to the second line.

    Workbooks.OpenText FileName:="c:securityMastertest.xls" _
    , Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
    Array(Array(0, 2), Array(31, 2), Array(67, 2), Array(93, 2), Array(105, 2))

    ActiveWorkbook.SaveAs FileName:= _
    "c:securitytest.xls ", FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False

    Is there a way that I can open Test.xls and have it copy mastertest.xls over test.xls and wipe out anything in test.xls while still open. Then save Test.xls before I continue with the report building.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Macro Problem - Saving Open spreadsheet (Excel 97)

    Daniel

    Your message is hard to understand, sorry but I guess you may want to look at the VBA help for SaveCopyAs.

    Why are you opening as text an Excel file? <Workbooks.OpenText FileName:="c:securityMastertest.xls>
    Why are you re-opening MasterTest.XLS when it is already open? You aid <Mastertest is the spreadsheet without any data and just the macros > So you are running the macro code from the workbook you are opening?!

    <<< Is there a way that I can open Test.xls and have it copy mastertest.xls over test.xls >>>
    Yes this is called SaveAs. You don't need to open workbooks, just save the workbook you want with a different name.

    <<< and wipe out anything in test.xls while still open >>>
    No because the workbook is open in memory and you have to close it and then open it again.

    Wassim
    PS If you use Mixed Case such as MasterTest.xls the file names are easier to read.
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Problem - Saving Open spreadsheet (Excel 97)

    Yes, I had problems with a server this morning and just wrote this on the fly

    I took your suggestion and removed the Workbook.OpenTextFileName and it made a big difference. But how do I get around popup "A file name TEST.XLS already exists in this location. Do you want to replace it:? - How can I get the macro to say yes or to ignore this.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro Problem - Saving Open spreadsheet (Excel 97)

    If you want to overwrite the file include the following line before the save code is executed :<pre> Application.DisplayAlerts = False</pre>

    Once your save code has executed restore things with :<pre> Application.DisplayAlerts = True</pre>

    Andrew C

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Problem - Saving Open spreadsheet (Excel 97)

    Thank you both on this. I know it was very badly writen message but with your help I was able to get it to work.

Posting Permissions

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