VBA to create CSV but not include blank rows

[SophieB]

Hi all

I've managed to cobble together some code (I'm new to this!) to create a CSV from a formulated tab in a spreadsheet. This new csv should be called the name of the tab plus today's date. It will then prompt the user as to where to save this csv.

It all works fine apart from 3 small things, which I'm sure are very simple if you know what you're doing...

1) My dates are coming out as mm/dd/yyyy rather than dd/mm/yyyy
2) I don't want to include columns V,W or X
3) In my formulated tab, I've used a lot of IFs which have returned "" but can't seem to work out how to not include these entirely blank rows in the output csv

Code below:

Function FormatShortdate()




Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(3)
sh.Range("M:M").NumberFormat = "dd/mm/yyyy"
End Function


Sub Export()
Dim MyPath As String
Dim MyFileName As String


MyFileName = "Abbas_Sage_Import_" & format(Date, "ddmmyyyy")


Sheets("Abbas-Sage Import").Copy
Set rng = Sheets("Abbas-Sage Import")


With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = "" '<~~ The start folder path for the file picker.
If .Show <> -1 Then GoTo NextCode
MyPath = .SelectedItems(1) & ""
End With


NextCode:


With ActiveWorkbook
.SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close False
End With
End Sub


I'd HUGELY appreciate some help with this!

Thank you

Sophie

 

[Yongle]

1) My dates are coming out as mm/dd/yyyy rather than dd/mm/yyyy

1 a csv is a text file and carries no format (although somehow dates are recognised as dates which Excel displays in non-regional default date format which is what you are experiencing)
- unless dates are converted to TEXT with dd/mm/yyyy format there is nothing you can do about this
- try opening a CSV file and formatting all cells to red font with font size =20. Now: save, close and re-open the csv file. No formatting!!

2) I don't want to include columns V,W or X

2 are these the last used columns?

3) In my formulated tab, I've used a lot of IFs which have returned "" but can't seem to work out how to not include these entirely blank rows in the output csv

3 is your data tabulated so that data filter can be used to show only the data required?

 

[SophieB]

Thanks for the reply!

I added in the TEXT formula which has sorted out the date, thank you

Columns V, W and X are indeed the last used columns, but they're there as "helper" columns for some other formulas so I don't want them including on the output

I have headers on my formulated tab so I could filter out the blank rows, but I'd rather not as there'll be a team of people using this sheet and the vast majority are not excel literate (hence my need create a nice easy button they can click to get the desired data rather than copy, pasting, filtering etc)... easier said than done I can tell you!

I hope this makes sense but if there's any further clarification needed please do let me know

Thanks again!

 

[Yongle]

I have headers on my formulated tab so I could filter out the blank rows,

We can use VBA to do the filtering automatically and reset it back to no filter afterwards

1. Are your headers in A1:X1?
2. Does data start in row2?
3. On which column can we base the filter?
(VBA will simple filter out Blanks in the same way as you would untick Blanks in DataFilter)

 

[SophieB]

That sounds great!

My headers are A1:X1
The data does start on row 2
We can base the filter on column D

Thank you!

 

[Yongle]

Test in a COPY of your file

1. Run this which simply creates a temporary sheet - are the pasted values what you expect?

Sub CreateTempSheet()

    Dim WS As Worksheet, Abbas As Worksheet
    Set Abbas = Sheets("Abbas-Sage Import")
    Set WS = Sheets.Add(After:=ActiveSheet)
'filter & copy the data
    With Abbas.Range("A1").CurrentRegion
        .AutoFilter Field:=4, Criteria1:="<>"
        .Resize(, 21).Copy WS.Range("A1")
        .AutoFilter Field:=4
    End With

    WS.Activate
        
    'INSERT YOUR CODE TO SELECT FOLDER &  SAVE TEMP SHEET HERE after testing content of temp sheet  (see 2)
  
'delete temporary sheet
    Application.DisplayAlerts = False
    'WS.Delete                          'remove apostrophe before WS after testing is complete (see 3)
    Application.DisplayAlerts = True
End Sub

2. If Temporary Sheet values are correct
- add your code to save as CSV
- amend your code to save sheet WS
(currently you are saving the workbook which has only one sheet which is why Excel does not complain, csv can only contain one sheet)
Instead of

With ActiveWorkbook

use

With WS

3. Next check that the csv looks correct
- if it is, remove the apostrophe and allow the temporary sheet to be deleted

Any problems report back and paste your code inside code tags
- click the # icon above post window and paste your code between the tags that appear
[ CODE ] paste code here [ /CODE ]

 

[SophieB]

Thank you so much for your work on this, I do appreciate it.

I've tried to follow your instructions to the letter but I'm now getting an "run-time error '1004' error. This appears no matter where I try to save the CSV (be it my desktop or somewhere on the server). My code is as follows:

Sub CreateTempSheet()

    Dim WS As Worksheet, Abbas As Worksheet
    Set Abbas = Sheets("Abbas-Sage Import")
    Set WS = Sheets.Add(After:=ActiveSheet)
'filter & copy the data
    With Abbas.Range("A1").CurrentRegion
        .AutoFilter Field:=4, Criteria1:="<>"
        .Resize(, 21).Copy WS.Range("A1")
        .AutoFilter Field:=4
    End With


    WS.Activate
        
    'INSERT YOUR CODE TO SELECT FOLDER &  SAVE TEMP SHEET HERE after testing content of temp sheet  (see 2)
  
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = "" '<~~ The start folder path for the file picker.
    If .Show <> -1 Then GoTo NextCode
    MyPath = .SelectedItems(1) & "\"
End With


NextCode:


With ActiveWorkbook
    .SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV, CreateBackup:=False
    .Close False
End With


  
'delete temporary sheet
    Application.DisplayAlerts = False
    WS.Delete                          'remove apostrophe before WS after testing is complete (see 3)
    Application.DisplayAlerts = True
End Sub

Apologies if it's something really simple, and again thank you!

 

[SophieB]

Sorry, I should have stated, when I change

With ActiveWorkbook

to

With WS

I get "Compile Error: Method or data member not found" error and the ".Close False" is highlighted

 

[Yongle]

No worries - it is the only way to learn.
VBA is always a bit of trial and error.

You are trying to save the whole workbook instead of the sheet - see post#6 note 2

 

[Yongle]

Our posts have crossed
Will test when I get back to my PC - it will be something minor. Cannot test your code on my phone