2

I have a 6-Worksheet excel spreadsheet where I would like to output 3 sheets as CSVs. I found this awesome macro: Save each sheet in a workbook to separate CSV files

which does it! I'm trying to modify it slightly but I'm fairly rusty on VB so I'm struggling. If anyone could give me some pointers on how to make these modifications to this macro that would be awesome:

  1. At the moment, it outputs all 6 sheets as CSVs... I only need 3, I could in this instance hard-link the names. I see that the macro loops over Worksheets and exports it, what is the best way of altering what the Worksheets array contains?

  2. This spreadsheet is being used to generate configuration files, so its used for different sized batches at different times. On the sheets in question, I have the formulas "filled" down to 100 lines... so if its used for say 10 lines, 90 lines of comma's need to be removed from the end of the CSVs. Bt this numbe is varible...I was thinking, it'd be great to hve it look at the input sheet (called "Data") and see how many lines were filled in to decide how many lines to export?

Looking at the existing Macro, I want to set the "EndRow" variable to the last row on the "Data" sheet that has something in column M. So I need to loop over the column, test the cell for contents then update the variabl but how do I select the sheet/column to do that?

3) Can I apply this macro to a button on one of the sheets?

Community
  • 1
  • 1
Bryn
  • 31
  • 5

2 Answers2

1

1) When it loops through the sheets, you could at that point prompt the user with a msgbox "Do you want to export ? If they select 'yes', then it will export. If they select 'no', it will skip it and go to the next sheet:

For Each wsSheet In Worksheets
    wsSheet.Activate
    If MsgBox("Do you want to export " & wsSheet.Name & " ?", vbYesNo) = vbYes Then
        'User has selected to export that sheet
        nFileNum = FreeFile
        Open csvPath & "\" & _
          wsSheet.Name & ".csv" For Output As #nFileNum
        ExportToTextFile CStr(nFileNum), Sep, False
        Close nFileNum
    End If
Next wsSheet

2) What are the empty rows currently showing if there is no data in them? Can you post a formula that is in those 100 rows?

3) You sure can. Here are some good sites you can learn this from:

Taptronic
  • 5,129
  • 9
  • 44
  • 59
Estate Master
  • 193
  • 3
  • 13
  • 1) I'll have a look at that but its always the same 3 sheets that need exporting so would before just to set it. 2) Nothing- the formulas are set to only fill the cell if the line is in use eg: '=IF(Data!A2<>"",Data!A2,"")'. But because the cell is used the export adds the lines as blank sections of commas 3) I'll have a read, thank you! – Bryn Aug 05 '11 at 01:23
  • 1) Solved, added an array to Worksheets on the For loop containing the 3 sheets I want to export: For Each wsSheet In Worksheets(Array("UDP", "Users", "Unity")) 3) The links abve solved, thanks- added CommandButton, works nicely. 2) Still need to solve. – Bryn Aug 05 '11 at 02:32
  • I think you might have to write a macro that will delete the cells where the formulas return "" http://www.excelbanter.com/showthread.php?t=13416 – Estate Master Aug 05 '11 at 03:35
  • This is a very nice addition to that macro that you found. +1 from me. – Taptronic Sep 22 '11 at 01:56
1

  1. Solved, added an array to Worksheets on the For loop containing the 3 sheets I want to export: 

    For Each wsSheet In Worksheets(Array("UDP", "Users", "Unity"))

  2. Solved, changed Endow from existing formula to (outside the With): 

    EndRow = ThisWorkbook.Worksheets("Data").Range("M" & Rows.Count).End(xlUp).Row

  3. The links abve solved, thanks- added CommandButton, works nicely.

Jeff Atwood
  • 63,320
  • 48
  • 150
  • 153
Bryn
  • 31
  • 5