Macro to save non empty cell data to csv file

[asgjim]

I've been tasked with taking a system design tool and exporting the data in a specific format. It should be fairly easy but I'm completely new when it comes to macros. Basic objective is as follows:
We have a 3 column worksheet with headers. Item, QTY, external id. I need to loop through each row, if column a (item) is blank, don't include the row in the export. Once it's finished the loop, it should export the worksheet as a csv file (with specific header names specified in the code). Ideally, this would be done on a button click on the worksheet. I've found several examples of macros that delete rows with empty cells, create a csv file from a worksheet, but non that really combine these.

The other important part is it should NOT alter the excel file / worksheets in any way. Just create the correctly formatted csv file.

Any help starting or pointing me in the right direction would be greatly appreciated. Let me know what other information I could provide that would help.

 

[asgjim]

OK, this is a really dumb question but how is there a special way I'm supposed to do that? Here is just a straight copy and paste. BTW, there is formulas in the blank cells. Sorry I didn't mention that earlier. Not sure how I'd copy those into this forum post. Thanks again for all your help.

QTY	PART
19	6291
2	3869
2	3871
2	2537
1	6135
	#N/A
	#N/A
1	1423
4	6350
1	6310
2	4982
	4981
16	4994
	2358
160	2384
46	5036
12	2431
6	2388
24	2393
74	2396
80	4999
40	2385
1	1791
1	6369

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[lewisgmorris]

I have put my file here for you to view

https://drive.google.com/folderview?id=0B6BOCseXW1yZdnNfSDhrR3o4WmM&usp=sharing

 

[asgjim]

I have put my file here for you to view

https://drive.google.com/folderview?id=0B6BOCseXW1yZdnNfSDhrR3o4WmM&usp=sharing

Thanks, it indeed works. I looked over the code and the only difference is the following line -

Set curWS = curWB.Worksheets("Sheet1")

I'm assuming it has to do with that. Could you explain a little bit about what this piece of code is doing? I originally left it as sheet1 but not having a sheet1 in my workbook it threw up the error "Run-time error '9': Subscript out of range.

Is there something in sheet1 that's needed for this to function correctly?

 

[asgjim]

OK, I realized that the sheet1 line is not it, as I changed your sample file to sheet5 and it worked just fine. I'm stumped. I copied and pasted your code and only changed the sheet name to the one I'm working on and still no go. I've exported the necessary sheets with my macro here - https://drive.google.com/file/d/0BwnbsOyA7QwiZzlJVlVmUDdDVFk/view?usp=sharing
Maybe you could look at my file and see what I'm doing wrong?

 

[lewisgmorris]

Ok your empty spaces actually output " " not "" so it technically its not empty.

instead of having;

If cell value = ""

I have put;

if cell value is a number

Sub Button1_Click()






Dim curWB As Workbook
Set curWB = ActiveWorkbook
Dim curWS As Worksheet


Dim newWB As Workbook
Set newWB = Workbooks.Add


Dim destfolder As String
Dim filename As String


destfolder = curWB.Path & "\"
filename = "output.csv"


Dim a As Range
Dim i As Integer


    For Each a In curWB.Worksheets("NETSUITE CSV").Range("A1:A" & curWB.Worksheets("NETSUITE CSV").Cells(Rows.Count, "A").End(xlUp).Row)
    
        If VBA.IsNumeric(a.Value) Then
            
            newWB.Sheets(1).Range("A1").Offset(i, 0) = a.Value
            newWB.Sheets(1).Range("A1").Offset(i, 1) = a.Offset(0, 1).Value
            newWB.Sheets(1).Range("A1").Offset(i, 2) = a.Offset(0, 2).Value
            i = i + 1
        
        End If
    
    Next a




newWB.Sheets(1).Columns.AutoFit


Application.DisplayAlerts = False
newWB.SaveAs destfolder & filename, xlCSV
newWB.Close
MsgBox "CSV File Generated to " & destfolder & filename
Application.DisplayAlerts = True


End Sub

How about that?

 

[asgjim]

Ok your empty spaces actually output " " not "" so it technically its not empty.

instead of having;

If cell value = ""

I have put;

if cell value is a number

Sub Button1_Click()






Dim curWB As Workbook
Set curWB = ActiveWorkbook
Dim curWS As Worksheet


Dim newWB As Workbook
Set newWB = Workbooks.Add


Dim destfolder As String
Dim filename As String


destfolder = curWB.Path & "\"
filename = "output.csv"


Dim a As Range
Dim i As Integer


    For Each a In curWB.Worksheets("NETSUITE CSV").Range("A1:A" & curWB.Worksheets("NETSUITE CSV").Cells(Rows.Count, "A").End(xlUp).Row)
    
        If VBA.IsNumeric(a.Value) Then
            
            newWB.Sheets(1).Range("A1").Offset(i, 0) = a.Value
            newWB.Sheets(1).Range("A1").Offset(i, 1) = a.Offset(0, 1).Value
            newWB.Sheets(1).Range("A1").Offset(i, 2) = a.Offset(0, 2).Value
            i = i + 1
        
        End If
    
    Next a




newWB.Sheets(1).Columns.AutoFit


Application.DisplayAlerts = False
newWB.SaveAs destfolder & filename, xlCSV
newWB.Close
MsgBox "CSV File Generated to " & destfolder & filename
Application.DisplayAlerts = True


End Sub

How about that?

Hey,
I just discovered that myself after some trial and error. Before I saw your post I ended up changing the if statement to this:

 If a.Value <> "" And a.Value <> " " And a.Value <> 0 Then            
            newWB.Sheets(1).Range("A1").Offset(i, 0) = a.Value
            newWB.Sheets(1).Range("A1").Offset(i, 1) = a.Offset(0, 1).Value
            newWB.Sheets(1).Range("A1").Offset(i, 2) = a.Offset(0, 2).Value
            i = i + 1
        
        End If

This worked. Thank you so much for all of your help!

 

[lewisgmorris]

I would change it to my version - if there is a cell with " " or more then it will again crash.

Good luck

Thanks

 

[asgjim]

I would change it to my version - if there is a cell with " " or more then it will again crash.

Good luck

Thanks

Thanks for the tip, I've updated my code to your version. This is exactly what I needed, it even works in office for mac

 

[imellor]

Sub copyDataAndGenerateCSV()


Dim curWB As Workbook
Set curWB = ActiveWorkbook
Dim curWS As Worksheet
Set curWS = curWB.Worksheets("Sheet1")


Dim newWB As Workbook
Set newWB = Workbooks.Add


Dim destfolder As String
Dim filename As String


destfolder = curWB.Path & "\"
filename = "output.csv"


Dim a As Range
Dim i As Integer


    For Each a In curWB.Worksheets("Sheet5").Range("A1:A" & curWB.Worksheets("Sheet5").Cells(Rows.Count, "A").End(xlUp).Row)
    
        If a.Value <> "" Then
            
            newWB.Sheets(1).Range("A1").Offset(i, 0) = a.Value
            newWB.Sheets(1).Range("A1").Offset(i, 1) = a.Offset(0, 1).Value
            newWB.Sheets(1).Range("A1").Offset(i, 2) = a.Offset(0, 2).Value
            i = i + 1
        
        End If
    
    Next a




newWB.Sheets(1).Columns.AutoFit


Application.DisplayAlerts = False
newWB.SaveAs destfolder & filename, xlCSV
newWB.Close
MsgBox "CSV File Generated to " & destfolder & filename
Application.DisplayAlerts = True


End Sub

Hi All,

I'm a newbie at VB code, I'm receiving a Application-defined or object-defined error error '1004"
The debug stops at the for loop, syntax looks OK, am I missing something?

Ian