none
Exporting Excel Data to CSV VBScript RRS feed

  • Question

  • Hi,

    I'm having some trouble with a little VBScript I have put together to export data into a CSV file.

    The VBScript goes through each individual Row in excel and basically exports it to a CSV file in the format I need.

    Dim JobHeader As JobHeader
    Dim JobDetails As JobDetails
    Dim FileNameStr As String
    Dim RowStart As Integer
    Dim MsgResponse As Integer
    
    Option Explicit
    
    Private Type JobHeader
        JHShipName As String                   'Col 3
        JHStreet As String                     'Col 5
        JHBlock As String                      'Col 6
        JHCity As String                       'Col 7
        JHState As String                      'Col 8
        JHCountry As String                    'Col 9
        JHZipCode As String                    'Col 10
        JHItemNo As String                     'Col 11
        JHOrderRef As String                   'Col 13
        JHItemDescription As String            'Col 15
        JHQuantity As String                   'Col 16
        JHWeight As String                     'Col 17
        
    End Type
    
    Private Type JobDetails
        JDShipName As String                   'Col 3
        JDStreet As String                     'Col 5
        JDBlock As String                      'Col 6
        JDCity As String                       'Col 7
        JDState As String                      'Col 8
        JDCountry As String                    'Col 9
        JDZipCode As String                    'Col 10
        JDItemNo As String                     'Col 11
        JDOrderRef As String                   'Col 13
        JDItemDescription As String            'Col 15
        JDQuantity As String                   'Col 16
        JDWeight As String                     'Col 17
    End Type
    
    Sub WriteFile()
       
        'Sets the row start point
        RowStart = 3
    
        Open FileNameStr For Output As #1
        
        JobHeader.JHShipName = "DeliveryAddressName"               'Col 3
        JobHeader.JHStreet = "DeliveryAddress1"                    'Col 5
        JobHeader.JHBlock = "DeliveryAddress2"                     'Col 6
        JobHeader.JHCity = "DeliveryAddress3"                      'Col 7
        JobHeader.JHState = "DeliveryAddress4"                     'Col 8
        JobHeader.JHCountry = "DeliveryAddress5"                   'Col 9
        JobHeader.JHZipCode = "DeliveryPostcode"                   'Col 10
        JobHeader.JHOrderRef = "OrderNumber"                       'Col 13
        JobHeader.JHItemDescription = "GoodsDescription"           'Col 15
        JobHeader.JHQuantity = "Pallets"                           'Col 16
        JobHeader.JHWeight = "Weight"                              'Col 17
    
        
        Write #1, JobHeader.JHShipName, JobHeader.JHStreet, JobHeader.JHBlock, JobHeader.JHCity, _
        JobHeader.JHState, JobHeader.JHCountry, JobHeader.JHZipCode, JobHeader.JHOrderRef, _
        JobHeader.JHItemDescription, JobHeader.JHQuantity, JobHeader.JHWeight
        
        Do
            'Checks to see if the first field is empty, if so closes the file
            If Cells(RowStart, 1).Value = "" Then
                GoTo CloseFile
            Else
                Call ProcessJobs
                Call WritesJobs
            End If
        Loop
        
    CloseFile:
        Close #1
    
    End Sub
    
    Sub ProcessJobs()
           
            JobDetails.JDShipName = Cells(RowStart, 3).Value                   'Col 3
            JobDetails.JDStreet = Cells(RowStart, 5).Value                     'Col 5
            JobDetails.JDBlock = Cells(RowStart, 6).Value                      'Col 6
            JobDetails.JDCity = Cells(RowStart, 7).Value                       'Col 7
            JobDetails.JDState = Cells(RowStart, 8).Value                      'Col 8
            JobDetails.JDCountry = Cells(RowStart, 9).Value                    'Col 9
            JobDetails.JDZipCode = Cells(RowStart, 10).Value                   'Col 10
            JobDetails.JDOrderRef = Cells(RowStart, 12).Value                  'Col 13
            JobDetails.JDItemDescription = Cells(RowStart, 15).Value & " x " & "(" & Cells(RowStart, 11).Value & ") " & Cells(RowStart, 14).Value
            JobDetails.JDQuantity = Cells(RowStart, 15).Value                  'Col 16
            JobDetails.JDWeight = Cells(RowStart, 16).Value                    'Col 17
            RowStart = RowStart + 1
             
    End Sub
    
    Sub WritesJobs()
    
            Write #1, JobDetails.JDShipName, JobDetails.JDStreet, JobDetails.JDBlock, JobDetails.JDCity, JobDetails.JDState, _
            JobDetails.JDCountry, JobDetails.JDZipCode, JobDetails.JDOrderRef, JobDetails.JDItemDescription, JobDetails.JDQuantity, _
            JobDetails.JDWeight
        
    End Sub
    This is working perfectly however there can be multiple lines of different product for the same Order Number and I need to consolidate this into one single line where the JDItemDescription, JDQuantity and JDWeight are updated with the combined data.

    I have attached a copy of the Spreadsheet so hopefully you can understand what I am after.

    http://www.expectdistribution.com/OrderExport.xls

    Thanks

    Tuesday, May 15, 2012 12:34 PM

All replies

  • When you have a "different product", how do you want to consolidate, for example,  JDItemDescription? Concatenate the values with space between?

    HTH, Bernie

    Tuesday, May 15, 2012 1:31 PM
  • Hi,

    If a product line has the same JDOrderRef I need to consolidate all the lines with that order number into one so JDItemDescription would be concatenated with a space, JDQuantity would be all of them added together and the same with JDWeight.

    Tuesday, May 15, 2012 1:52 PM
  • Change

        Do
           
    'Checks to see if the first field is empty, if so closes the file
           
    If Cells(RowStart, 1).Value = "" Then
               
    GoTo CloseFile
           
    Else
               
    Call ProcessJobs
               
    Call WritesJobs
           
    End If
       
    Loop

    to

        Do
        'Checks to see if the first field is empty, if so closes the file
        If Cells(RowStart, 1).Value = "" Then
            GoTo CloseFile
        Else
        'Change the 12 to whatever column has the order number - unclear from your code
        'because the code uses 12 and the comment has 13
          If Cells(RowStart, 12).Value <> Cells(RowStart - 1, 12).Value Then Call ProcessJobs
          If Cells(RowStart, 12).Value = Cells(RowStart - 1, 12).Value Then Call AddJobs
          If Cells(RowStart, 12).Value <> Cells(RowStart + 1, 12).Value Then Call WritesJobs
        End If
    Loop

    Change these data types:

        JDQuantity As String                   'Col 16
        JDWeight
    As String                     'Col 17
    to

        JDQuantity As Double    'Col 16
        JDWeight
    As Double   'Col 17

    and add a sub:

    Sub AddJobs()
       
         JobDetails.JDItemDescription = JobDetails.JDItemDescription & " " & _
         Cells(RowStart, 15).Value & " x " & "(" & Cells(RowStart, 11).Value & ") " & Cells(RowStart, 14).Value
         JobDetails.JDQuantity = JobDetails.JDQuantity + Cells(RowStart, 15).Value  ' Col 16 or 15?
         JobDetails.JDWeight = JobDetails.JDWeight + Cells(RowStart, 16).Value   'Col 17 or 16
         RowStart = RowStart + 1
         
    End Sub


    HTH, Bernie

    Tuesday, May 15, 2012 2:35 PM