Converting keep statements to VBA

[millerm]

Hi,

I'm currently trying to convert some SAS code into VBA, as we no longer have the package which allows you to import from .xlsx, so I'm having to convert all my sheets to CSV before bringing them into SAS. I'm essentially trying to select some worksheets and define which columns to keep, as would be done in a one-line keep statements in SAS. Here's the VBA code so far:

Sub ExportSheetsToCSV()
    Dim xWs As Worksheet
    Dim xcsvFile As String
    For Each xWs In Application.ActiveWorkbook.Worksheets(Array("0 (C)", "2 PostcodeGroupingTable", "11 Region Based Loading CLS", "13 Postcode SP CLS",  "15 Risk Score SP CLS", "19 Flat Fee Supplement"))
        If xWs.Name = "0 (C)" Then xWs.Range("A1:A561,C1:C561").Select
        Selection.Copy
        'Change the file to suit the relevant destination folder
        xcsvFile = "X:\Dept\_2019_05_16\Macro_Test" & "\" & xWs.Name & ".csv"
        Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, _
        FileFormat:=xlCSV, CreateBackup:=False
        Application.ActiveWorkbook.Saved = True
        Application.ActiveWorkbook.Close
    Next
End Sub

I'm hoping then to copy the selection/range statement to have a defined range for each spreadsheet/csv separately.

At the moment the process is working, however it's just ignoring the range thing completely.

Sorry for testing your brains with VBA, the hope is someone knows an easy conversion!

[Reeza]

Any reason to not do a mass conversion of each sheet and keep the KEEP statement in the SAS code? I have a script that will convert all XLSX to CSV but it's currently not set up to handle multiple sheets.
https://gist.github.com/statgeek/878e585102c14e01581f55dbe972d27e

[millerm]

Hi Reeza,

I've wanted to keep it in SAS and build the drop/keep statements there, but my brief is to do it all in VBA for the ease of colleagues who don't use SAS much.

Also, my converted CSVs have very strange ranges even when just exported from excel manually, so I was hoping to explicitly define in the VBA what ranges to import

I hope that makes sense? It's tantamount to a keep statement but I was looking for the conversion (direct or janky)

[Reeza]

But wouldn't each sheet then have a custom range which would be hard to account for somehow?

[millerm]

My hope was that I could have the if statement (coped below) copied for each sheet, because sometimes values are being cut off which isn't helpful, or there's loads of extra lines taking up lots of memory

        If xWs.Name = "0 (C)" Then xWs.Range("A1:A561,C1:C561").Select
        xWs.Copy

Maybe this is too aspirational  

[millerm · Accepted solution]

UPDATE:

I broke and gave up on VBA and have commented so much on the SAS version that it can't not be understood

[AlanC]

Late to the party but that is ok.

I can probably figure out the VBA but rarely work with it anymore.

Suggestion for handling:

1. Get one of 2 products (both free)

  - Visual Studio Code

  - Visual Studio Express (Community Edition) - recommended

2. Install NuGet package EPPLus

3. Look at their examples in C# and lift samples

4. Execute

Yes, I know the above may be out of a comfort zone but it is far, far, far better than VBA. You don't even need Excel installed on the machine. It would take you a little bit to understand it, at first, but then you would blaze with Excel.  Plus, it comes with Intellisense so you don't have to hunt and peck for parameters.

If it something you want to consider, I can help you get something simple started. Just ping me.Would take 15 mins. There are also loads of tutorials and videos out there.