Excel VBA Macro - exporting csv files (0kB)

Microsoft Excel 2003 (full product)
December 1, 2010 at 13:40:10
Specs: Windows XP
Hello Everyone:

I have a VBA macro designed to take a whole bunch of Modbus addresses entered into a list and then export specifically-named csv files from Excel. My code is below. It works beautifully, with one small problem.

When I enter addresses into the list sometimes not all subsections of the list are filled, in which case there should not be a csv file created for that particular subsection. Unfortunately I'm getting some csv files that are 0 kB exported as a result. This wouldn't be a big deal except I grab all of the exported csv's at once and then import them into my other piece of software. Because of the way they are named the importing software does all the work!

To use this sheet I put the title name in column A and copy it all the way down. Column B gets the subset name (could be a variety of things) Column C has a listing of attributes under each subsection name, and finally column D is where I 10-key in the Modbus addresses, where applicable.

Anyone have any ideas? I'm not sure how to paste my code into here and made it actually look like code either. If anyone could advise for future posts I would certainly appreciate it.

Thanks so much!


Sub Export()

Dim num_rows As Integer
Dim filename As String
Dim Filelocation As String

'Save the CSV Files to this location
Filelocation = "C:\Documents and Settings\CSV_Exports"

'Cell A5 is using the counta function, taking a look at the total entries in column A for which to apply these forumlas
num_rows = Range("A5").Value
filename = Worksheets("Master_List").Cells(7, 1) & "_" & Worksheets("Master_List").Cells(7, 2) & ".csv"
Open (Filelocation & "\" & filename) For Output As #1
For i = 1 To num_rows
If Worksheets("Master_List").Cells(6 + i, 1) & "_" & Worksheets("Master_List").Cells(i + 6, 2) & ".csv" = filename Then
If Worksheets("Master_List").Cells(6 + i, 4) <> "" Then
Print #1, CStr(Worksheets("Master_List").Cells(6 + i, 3) & "," & Worksheets("Master_List").Cells(6 + i, 4))
End If
Else
If Worksheets("Master_List").Cells(6 + i, 2) <> "" Then
Close #1
filename = Worksheets("Master_List").Cells(6 + i, 1) & "_" & Worksheets("Master_List").Cells(i + 6, 2) & ".csv"
Open (Filelocation & "\" & filename) For Output As #1
If Worksheets("Master_List").Cells(6 + i, 4) <> "" Then
Print #1, CStr(Worksheets("Master_List").Cells(6 + i, 3) & "," & Worksheets("Master_List").Cells(6 + i, 4))
End If
End If
End If
Next i
Close
End Sub


See More: Excel VBA Macro - exporting csv files (0kB)

Report •


#1
December 2, 2010 at 06:35:34
1 - To post VBA code correctly (and thanks for asking!) click on the How To in my signature line.

2 - I haven't really looked at your code (yet) to see what it's doing, but maybe you could explain this statement a little more:

When I enter addresses into the list sometimes not all subsections of the list are filled, in which case there should not be a csv file created for that particular subsection.

What is a "subsection"? Is it a range? Is there something we can key off of so the code will say "Hey, that range/cell is empty, skip the csv creation"?

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 2, 2010 at 11:16:06
Easier method: After exporting, iterate though the directory, and delete any empty files.

How To Ask Questions The Smart Way


Report •

#3
December 2, 2010 at 11:26:45
Why is that easier than never creating the files in the first place by simply skipping over the code that creates the file?

Generically, it could look something like this:

Sub Create_csv()
 If Range("A1") = "" Then GoTo no_csv
  'csv creation code goes here
 no_csv:
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
December 2, 2010 at 12:19:33
DerbyDad03: Why is that easier
1) It's easier for me; I don't have to attempt to read the logic.
2) The print logic is in multiple locations.
3) GoTo is evil and should be avoided.

How To Ask Questions The Smart Way


Report •

#5
December 8, 2010 at 10:01:35
When I enter addresses into the list sometimes not all subsections of the list are filled, in which case there should not be a csv file created for that particular subsection.
What is a "subsection"? Is it a range? Is there something we can key off of so the code will say "Hey, that range/cell is empty, skip the csv creation"?

Here is an example of a row in my sheet


Site Name........ Subsection........ Attribute........ ...Modbus Addr

Site_XYZ ..........DRCT_OPS .......ManualRun........18


Here is my present code

Sub Export()



Dim num_rows As Integer
Dim filename As String
Dim Filelocation As String


'Save the CSV Files to this location
Filelocation = "C:\Documents and Settings\e02086\Desktop\CSV_Exports"

'Cell A5 is using the counta function, taking a look at the total entries in column A for which to apply these forumlas
num_rows = Range("A5").Value


filename = Worksheets("Master_List").Cells(7, 1) & "_" & Worksheets("Master_List").Cells(7, 2) & ".csv"

Open (Filelocation & "\" & filename) For Output As #1

For i = 1 To num_rows

    If Worksheets("Master_List").Cells(6 + i, 1) & "_" & Worksheets("Master_List").Cells(i + 6, 2) & ".csv" = filename Then
        If Worksheets("Master_List").Cells(6 + i, 4) <> "" Then
            Print #1, CStr(Worksheets("Master_List").Cells(6 + i, 3) & "," & Worksheets("Master_List").Cells(6 + i, 4))
        
        End If
    
    Else
        If Worksheets("Master_List").Cells(6 + i, 2) <> "" Then
        Close #1
        
        filename = Worksheets("Master_List").Cells(6 + i, 1) & "_" & Worksheets("Master_List").Cells(i + 6, 2) & ".csv"
        
        Open (Filelocation & "\" & filename) For Output As #1
            If Worksheets("Master_List").Cells(6 + i, 4) <> "" Then
                Print #1, CStr(Worksheets("Master_List").Cells(6 + i, 3) & "," & Worksheets("Master_List").Cells(6 + i, 4))
            
            End If
    
    
        End If

    End If
Next i

Close

End Sub


Report •

Ask Question