Creating CSV File From External Data Source

T

testsubjec

New Member
Joined
Jul 14, 2011
Messages
33
Hi All,

We have an issue at my work place where users have to manually input jobs into our Transport System from our Warehouse System. This is a very time consuming process and with all third party software houses they want to charge us thousands to setup a link. So I decided to do it myself and so far I'm nearly there....

I'm working with Excel 2010 and we have a worksheet which is powered via a SQL View. This SQL View pulls back data from the Order Pool in our Warehouse System and this allows the users to filter the orders which they wish to transfer to our Transport System. Once they have done this they then click on a macro button to export the filtered orders out into a CSV file.

Code: 
Dim JobHeader As JobHeader
Dim JobDetails As JobDetails
Dim FileNameStr As String
Dim RowStart As Integer
Dim MsgResponse As Integer
Dim JobNumber As Integer

Option Explicit

'Builds Header Row for File
Private Type JobHeader
    
    JHAccountCode As String               'Column 2 (B) - Acount Code
    JHOrderRef As String                  'Column 4 (D) - Customer Order Number
    JHDeliveryDate As String              'Column 6 (F) - Delivery Date
    JHDeliveryAddressName As String       'Column 7 (G) - Delivery Address Name
    JHDeliveryAddressLine1 As String      'Column 8 (H) - Delivery Address Line 1
    JHDeliveryAddressLine2 As String      'Column 9 (I) - Delivery Address Line 2
    JHDeliveryAddressLine3 As String      'Column 10 (J) - Delivery Address Line 3
    JHDeliveryAddressLine4 As String      'Column 11 (K) - Delivery Address Line 4
    JHDeliveryAddressLine5 As String      'Column 12 (L) - Delivery Address Line 5
    JHDeliveryAddressPostCode As String   'Column 13 (M) - Delivery Address Post Code
    JHDeliveryTelephone As String         'Column 14 (N) - Delivery Telephone
    JHDeliveryInstructions As String      'Column 15 (O) - Delivery Instructions
    JHGoodsDescription As String          'Column 16 (P) + Column 17 (Q) + Column 18 (R)- Goods Description ** This is generated on the fly **
    JHPalletQuantity As String            '** This is generated on the fly **
    JHPalletVolumne As String             '** This is generated on the fly **
    JHPalletWeight As String              'Column 19 (S) - Pallet Weight
    
End Type


'Builds Data Row for File
Private Type JobDetails
    
    JDAccountCode As String               'Column 2 (B) - Acount Code
    JDOrderRef As String                  'Column 4 (D) - Customer Order Number
    JDDeliveryDate As String              'Column 6 (F) - Delivery Date
    JDDeliveryAddressName As String       'Column 7 (G) - Delivery Address Name
    JDDeliveryAddressLine1 As String      'Column 8 (H) - Delivery Address Line 1
    JDDeliveryAddressLine2 As String      'Column 9 (I) - Delivery Address Line 2
    JDDeliveryAddressLine3 As String      'Column 10 (J) - Delivery Address Line 3
    JDDeliveryAddressLine4 As String      'Column 11 (K) - Delivery Address Line 4
    JDDeliveryAddressLine5 As String      'Column 12 (L) - Delivery Address Line 5
    JDDeliveryAddressPostCode As String   'Column 13 (M) - Delivery Address Post Code
    JDDeliveryTelephone As String         'Column 14 (N) - Delivery Telephone
    JDDeliveryInstructions As String      'Column 15 (O) - Delivery Instructions
    JDGoodsDescription As String          'Column 16 (P) + Column 17 (Q) + Column 18 (R)- Goods Description ** This is generated on the fly **
    JDPalletQuantity As String            '** This is generated on the fly **
    JDPalletVolumne As String             '** This is generated on the fly **
    JDPalletWeight As String              'Column 19 (S) - Pallet Weight
    
End Type


Sub OrderPoolExport()
    'Blanks all variables for Job Header
    JobHeader.JHAccountCode = ""
    JobHeader.JHOrderRef = ""
    JobHeader.JHDeliveryDate = ""
    JobHeader.JHDeliveryAddressName = ""
    JobHeader.JHDeliveryAddressLine1 = ""
    JobHeader.JHDeliveryAddressLine2 = ""
    JobHeader.JHDeliveryAddressLine3 = ""
    JobHeader.JHDeliveryAddressLine4 = ""
    JobHeader.JHDeliveryAddressLine5 = ""
    JobHeader.JHDeliveryAddressPostCode = ""
    JobHeader.JHDeliveryTelephone = ""
    JobHeader.JHDeliveryInstructions = ""
    JobHeader.JHGoodsDescription = ""
    JobHeader.JHPalletQuantity = ""
    JobHeader.JHPalletVolumne = ""
    JobHeader.JHPalletWeight = ""
       
    'Blanks all variables for Job Details
    JobDetails.JDAccountCode = ""
    JobDetails.JDOrderRef = ""
    JobDetails.JDDeliveryDate = ""
    JobDetails.JDDeliveryAddressName = ""
    JobDetails.JDDeliveryAddressLine1 = ""
    JobDetails.JDDeliveryAddressLine2 = ""
    JobDetails.JDDeliveryAddressLine3 = ""
    JobDetails.JDDeliveryAddressLine4 = ""
    JobDetails.JDDeliveryAddressLine5 = ""
    JobDetails.JDDeliveryAddressPostCode = ""
    JobDetails.JDDeliveryTelephone = ""
    JobDetails.JDDeliveryInstructions = ""
    JobDetails.JDGoodsDescription = ""
    JobDetails.JDPalletQuantity = ""
    JobDetails.JDPalletVolumne = ""
    JobDetails.JDPalletWeight = ""
    
    'Blanks all variables for Params
    FileNameStr = ""
    RowStart = 0
    MsgResponse = 0
    JobNumber = 0
    
    
    'Message Box to confirm you want to export the jobs
    MsgResponse = MsgBox("NOTICE: This will now create a Import File " & vbCrLf & "Confirm Job(s) Export?", _
    vbInformation + vbYesNo, "Export Orders")
    
    'Sets the CSV filename to be Export and then the Date
    If MsgResponse = 6 Then
         
        FileNameStr = "C:\Desktop\Export " & Format(Date, "dd-mm-yyyy") & ".csv"
        Call WriteFile
    
    Else
       
        MsgResponse = MsgBox("Check Job Spreadsheet File", vbCritical)
    
    End If

End Sub

Sub WriteFile()
   
    'Sets the row start point
    RowStart = 5

    'Creates Output File Object
    Open FileNameStr For Output As #1
     
    JobHeader.JHAccountCode = "AccountCode"                      'Column 2 (B) - Acount Code
    JobHeader.JHOrderRef = "OrderNumber"                         'Column 4 (D) - Customer Order Number
    JobHeader.JHDeliveryDate = "DeliveryDate"                    'Column 6 (F) - Delivery Date
    JobHeader.JHDeliveryAddressName = "DeliveryAddressName"      'Column 7 (G) - Delivery Address Name
    JobHeader.JHDeliveryAddressLine1 = "DeliveryAddressLine1"    'Column 8 (H) - Delivery Address Line 1
    JobHeader.JHDeliveryAddressLine2 = "DeliveryAddressLine2"    'Column 9 (I) - Delivery Address Line 2
    JobHeader.JHDeliveryAddressLine3 = "DeliveryAddressLine3"    'Column 10 (J) - Delivery Address Line 3
    JobHeader.JHDeliveryAddressLine4 = "DeliveryAddressLine4"    'Column 11 (K) - Delivery Address Line 4
    JobHeader.JHDeliveryAddressLine5 = "DeliveryAddressLine5"    'Column 12 (L) - Delivery Address Line 5
    JobHeader.JHDeliveryAddressPostCode = "DeliveryPostCode"     'Column 13 (M) - Delivery Address Post Code
    JobHeader.JHDeliveryTelephone = "DeliveryTelephone"          'Column 14 (N) - Delivery Telephone
    JobHeader.JHDeliveryInstructions = "DeliveryInstructions"    'Column 15 (O) - Delivery Instructions
    JobHeader.JHGoodsDescription = "GoodsDescription"            'Column 16 (P) + Column 17 (Q) + Column 18 (R)- Goods Description ** This is generated on the fly **
    JobHeader.JHPalletQuantity = "Pallets"                       '** This is generated on the fly **
    JobHeader.JHPalletVolumne = "Volume"                         '** This is generated on the fly **
    JobHeader.JHPalletWeight = "Weight"                          'Column 19 (S) - Pallet Weight
    
    'Writes the Header Row to the file
    Write #1, JobHeader.JHAccountCode, JobHeader.JHOrderRef, JobHeader.JHDeliveryDate, JobHeader.JHDeliveryAddressName, _
    JobHeader.JHDeliveryAddressLine1, JobHeader.JHDeliveryAddressLine2, JobHeader.JHDeliveryAddressLine3, JobHeader.JHDeliveryAddressLine4, _
    JobHeader.JHDeliveryAddressLine5, JobHeader.JHDeliveryAddressPostCode, JobHeader.JHDeliveryTelephone, JobHeader.JHDeliveryInstructions, _
    JobHeader.JHGoodsDescription, JobHeader.JHPalletQuantity, JobHeader.JHPalletVolumne, JobHeader.JHPalletWeight
    
    Do
    'Checks to see if the first field is empty, if so closes the file
    If Cells(RowStart, 1).Value = "" Then
        GoTo CloseFile
    Else
      'Small process to combine multiple order lines of the same order into one line
      If Cells(RowStart, 4).Value <> Cells(RowStart - 1, 4).Value Then Call ProcessJobs
      If Cells(RowStart, 4).Value = Cells(RowStart - 1, 4).Value Then Call AddJobs
      If Cells(RowStart, 4).Value <> Cells(RowStart + 1, 4).Value Then Call WritesJobs
      RowStart = RowStart + 1
    End If
Loop
    
CloseFile:
    Close #1

End Sub


Sub ProcessJobs()
    'Sub Routine to collect the data out of the cells and write it to the Type
        
    JobDetails.JDAccountCode = UCase(Cells(RowStart, 2).Value)
    JobDetails.JDOrderRef = UCase(Cells(RowStart, 4).Value)
    JobDetails.JDDeliveryDate = UCase(Cells(RowStart, 6).Value)
    JobDetails.JDDeliveryAddressName = UCase(Cells(RowStart, 7).Value)
    JobDetails.JDDeliveryAddressLine1 = UCase(Cells(RowStart, 8).Value)
    JobDetails.JDDeliveryAddressLine2 = UCase(Cells(RowStart, 9).Value)
    JobDetails.JDDeliveryAddressLine3 = UCase(Cells(RowStart, 10).Value)
    JobDetails.JDDeliveryAddressLine4 = UCase(Cells(RowStart, 11).Value)
    JobDetails.JDDeliveryAddressLine5 = UCase(Cells(RowStart, 12).Value)
    JobDetails.JDDeliveryAddressPostCode = UCase(Cells(RowStart, 13).Value)
    JobDetails.JDDeliveryTelephone = UCase(Cells(RowStart, 14).Value)
    JobDetails.JDDeliveryInstructions = UCase(Cells(RowStart, 15).Value)
    JobDetails.JDGoodsDescription = UCase(Cells(RowStart, 16).Value & " x " & "(" & Cells(RowStart, 17).Value & ") " & Cells(RowStart, 18).Value)
    'JobDetails.JDPalletQuantity = ""
    'JobDetails.JDPalletVolumne = ""
    JobDetails.JDPalletWeight = UCase(Cells(RowStart, 19).Value)
         
End Sub

Sub AddJobs()
    'If previous Order Number found consolidate the order line into one line
     
    'Builds the Goods Description using QTY, Product Item and Item Description
    JobDetails.JDGoodsDescription = UCase(JobDetails.JDGoodsDescription & vbCrLf & _
    Cells(RowStart, 16).Value & " x " & "(" & Cells(RowStart, 17).Value & ") " & Cells(RowStart, 18).Value)
     
    'Totals up the Weight
    JobDetails.JDPalletWeight = JobDetails.JDPalletWeight + Cells(RowStart, 19).Value
     
End Sub

Sub WritesJobs()
    'Write the CSV file
    Write #1, JobDetails.JDAccountCode, JobDetails.JDOrderRef, JobDetails.JDDeliveryDate, JobDetails.JDDeliveryAddressName, _
    JobDetails.JDDeliveryAddressLine1, JobDetails.JDDeliveryAddressLine2, JobDetails.JDDeliveryAddressLine3, JobDetails.JDDeliveryAddressLine4, _
    JobDetails.JDDeliveryAddressLine5, JobDetails.JDDeliveryAddressPostCode, JobDetails.JDDeliveryTelephone, JobDetails.JDDeliveryInstructions, _
    JobDetails.JDGoodsDescription, JobDetails.JDPalletQuantity, JobDetails.JDPalletVolumne, JobDetails.JDPalletWeight
    
End Sub

The problem that I have got is, it's exporting all the rows and not just the filtered rows.

If someone could point me in the right directions it would be appreciated.

Thanks!!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Hi T,
I think you could filter out hidden rows by using the Height attribute, like adding to your code an if / end if
Code: 
...
 Do
    'Checks to see if the first field is empty, if so closes the file
    If Cells(RowStart, 1).Value = "" Then
        GoTo CloseFile
    Else
      ' Filters out hidden rows
      If Cells(RowStart, 1).Height > 0 Then
         'Small process to combine multiple order lines of the same order into one line
         If Cells(RowStart, 4).Value <> Cells(RowStart - 1, 4).Value Then Call ProcessJobs
         If Cells(RowStart, 4).Value = Cells(RowStart - 1, 4).Value Then Call AddJobs
         If Cells(RowStart, 4).Value <> Cells(RowStart + 1, 4).Value Then Call WritesJobs
      end if
      RowStart = RowStart + 1
    End If
Loop
...

I hope this helps
Cheers
Sergio
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
VBA split file based and send splitted file in separate emails
Replies
0
Views
232
harmless92
H
S
  • Solved
How to stop table created from the Power Query Editor from refreshing twice
Replies
2
Views
113
sspatriots
S
I
  • Question
Modify VBA script to count rows with data in multiple sheets
Replies
0
Views
145
irishjsk
I
S
  • Question
VBA to ignore certain MAC Address values but process all others
Replies
1
Views
287
myall_blues
myall_blues
S
  • Solved
Need value to be the Table Column Header in lieu of Cell Address
Replies
1
Views
299
sspatriots
S

Forum statistics

Threads
1,216,084
Messages
6,128,721
Members
449,465
Latest member
TAKLAM

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top