+ Reply to Thread
Results 1 to 12 of 12

Macros To Save Worksheet as New xlsx file & Save Another Worksheet As A Text File

  1. 11-16-2011, 07:23 AM #1
    KeithT is offline
    Registered User
    Join Date
    11-05-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Question Macros To Save Worksheet as New xlsx file & Save Another Worksheet As A Text File

    Assistance appreciated please.

    I’m looking for a couple of macros to do the following on a worksheet as per the attached example, ExampleData_04.xlsx:-

    Macro 1 – To save the sheet “Data Sheet” as a new xlsx file with values and formatting, and use the name taken from cell C8 on the “Reference “ sheet for the filename. The “Data Sheet” sheet will be of variable length and will include blank rows.

    Macro 2 – To save the contents of column A in sheet “Config” down to the last row that is used, into a text file using the name taken from cell C6 on the “Reference” sheet for the filename. The “Config” sheet will be of variable length up to approx 3000 rows and will include blank rows.

    I believe that this can be done using shell commands but this is not something that I have experience of. Any help would be greatly appreciated.

    Thanks

    Keith
    Attached Files Attached Files
    Last edited by KeithT; 11-16-2011 at 05:15 PM.
    Reply With QuoteRegister To Reply
  2. 11-16-2011, 08:04 AM #2
    Steffen Thomsen is offline
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Macros To Save Worksheet as New xlsx file & Save Another Worksheet As A Text File 

    Please Login or Register  to view this content.
    *EDIT reason: Wrong fileformat
    Last edited by Steffen Thomsen; 11-16-2011 at 08:10 AM.
    Reply With QuoteRegister To Reply
  3. 11-16-2011, 11:15 AM #3
    KeithT is offline
    Registered User
    Join Date
    11-05-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Macros To Save Worksheet as New xlsx file & Save Another Worksheet As A Text File

    Hi Steffen,

    Thanks for your reply. I've tried the code but it fails at Print #1, Cells(i, 1)

    Regards

    Keith
    Reply With QuoteRegister To Reply
  4. 11-16-2011, 11:36 AM #4
    Steffen Thomsen is offline
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Macros To Save Worksheet as New xlsx file & Save Another Worksheet As A Text File

    Sorry i mistyped in the line above

    Change 

    Please Login or Register  to view this content.
    To


    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 11-16-2011, 11:42 AM #5
    Steffen Thomsen is offline
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Macros To Save Worksheet as New xlsx file & Save Another Worksheet As A Text File

    Hi

    Code with a few changes

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 11-16-2011, 01:01 PM #6
    KeithT is offline
    Registered User
    Join Date
    11-05-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Macros To Save Worksheet as New xlsx file & Save Another Worksheet As A Text File

    Hi Steffen,

    Having tested and modified your earlier post I was just about to reply to explain the problems that were left. And now I've found that you've already spotted them so that's saved me some writing. Thank you.

    The code works brilliantly except for one thing. When the xlsx file is created I only want the sheet "Data Sheet" to be saved not the whole workbook.

    Regards

    Keith
    Reply With QuoteRegister To Reply
  7. 11-16-2011, 01:31 PM #7
    Steffen Thomsen is offline
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Macros To Save Worksheet as New xlsx file & Save Another Worksheet As A Text File

    Hi,

    It needed a bit of alteration as you can se, this should work though

    Please Login or Register  to view this content.
    Last edited by Steffen Thomsen; 11-16-2011 at 02:55 PM.
    Please take time to read the forum rules
    Reply With QuoteRegister To Reply
  8. 11-16-2011, 03:00 PM #8
    KeithT is offline
    Registered User
    Join Date
    11-05-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Macros To Save Worksheet as New xlsx file & Save Another Worksheet As A Text File

    Steffen,

    That works really well, but is it possible to save the new xlsx file to have the same settings as the original sheet. ie. Column widths, Sheet Header & Print settings (Landscape, fit to one sheet, print gridlines)?

    Thanks

    Keith
    Reply With QuoteRegister To Reply
  9. 11-16-2011, 03:24 PM #9
    Steffen Thomsen is offline
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Macros To Save Worksheet as New xlsx file & Save Another Worksheet As A Text File

    Hi,

    Here you are

    Please Login or Register  to view this content.
    Another time it would be best if you described your full question/outcome in your first post as this way off adding questions along the way makes for alot of rewriting.

    Steffen Thomsen
    Last edited by Steffen Thomsen; 11-16-2011 at 03:28 PM.
    Reply With QuoteRegister To Reply
  10. 11-16-2011, 04:40 PM #10
    KeithT is offline
    Registered User
    Join Date
    11-05-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Macros To Save Worksheet as New xlsx file & Save Another Worksheet As A Text File

    Steffen,

    Sincere appologies if you feel I've mislead you, that certainly wasn't the intention.

    I'd tried to make myself as clear as possible and I hoped that "save the sheet “Data Sheet” as a new xlsx file with values and formatting" would be enough but I can see now that I should have been more specific. As you know it was an Example workbook that I posted as I couldn't post the actual one that I'm working on. Unfortunately I forgot to format the Data Sheet the same as the original in respect to the sheet middle header and print setup which didn't help.

    I've tried the new code and it works, but still loses the sheet header and print settings. I've posted a new file ExampleData_05.xlsm which has the correct formatting for the Data Sheet and includes your code.

    Regards

    Keith
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 11-16-2011, 05:00 PM #11
    Steffen Thomsen is offline
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Macros To Save Worksheet as New xlsx file & Save Another Worksheet As A Text File

    Hi,

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 11-16-2011, 05:13 PM #12
    KeithT is offline
    Registered User
    Join Date
    11-05-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Macros To Save Worksheet as New xlsx file & Save Another Worksheet As A Text File

    Steffen,

    That works a treat. Thank you very much for your efforts and for your patience. Really appreciated.

    Regards

    Keith
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1