CODEWITHSUNDEEP
excelcsvvba
Bryn
Bryn

Reputation: 31

Saving worksheets as CSVs from excel

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?

Upvotes: 2

Views: 1173

Answers (2)

Estate Master
Estate Master

Reputation: 193

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:

Upvotes: 1

Bryn
Bryn

Reputation: 31

  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.

Upvotes: 1

Related Questions