urgent!Multiple sheets into csv! Quite a challenge!

A

abshaw

Board Regular
Joined
Feb 12, 2004
Messages
53
Hello guys, gurus and gods of excel.
I am doing an export as comma separated values file out of excel. i did manage to create the a csv file with all the sales people at the end of each day. there are two 3 sheets that i have as follows


Sheet 1

column a --> Salesperson ID
column b --> First name
column c --> Last name
column d --> effective date
column e --> term date

column a b c are text type data fields
column d e are date type data fields.


Sheet 2

column a --> Salesperson ID
column b --> Invoice no
column c --> Product name
column d --> amount

column a b c are text type data fields
column d is currency type data field.


Sheet 3

is currently blank and i am not using it.

(the column headers are not there in the actual sheet, but to explain, i gave them sample names, the actual sheet only has the data in it)

now lets say the first three lines of data are as follows

Sheet 1
6587 jake lumas 01/02/2004 05/02/2004
0543 mary edgar 01/02/2004
3545 james lomaki 01/02/2004 03/05/2004

Sheet 2
6587 td12548 facial cream 19.95
3545 td32548 massager 69.99
6587 td26584 foot cream 20.22


What i want is to create a single record for each person in the csv file by the name of dump in C:/temp folder so the full path will be C:/temp/dump.csv
what the final text file should look like is

line 1

"6587","jake","lumas","01/02/2004","05/02/2004","6587","td12548","facial cream","19.95","6587","td26584","foot cream","20.22"

line 2
"0543","mary edgar","01/02/2004",""

line 3
"3545","james","lomaki","01/02/2004","03/05/2004","3545","td32548","massager","69.99 "


it really doesnot matter how, but i need to get this up and running for tommorow morning, please help as its on my head and i am stuck with it.
thanks for your help in advance
 
Sort by date Sort by votes
Okay...I tested this with your sample data and it worked fine. Try it out and see if it works for you.

You will need to adjust the Sheet names as needed.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DumpSalesInfo()
    <SPAN style="color:#00007F">Dim</SPAN> SalesPeople <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">New</SPAN> Collection
    <SPAN style="color:#00007F">Dim</SPAN> Sales <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">New</SPAN> Collection
    
    <SPAN style="color:#00007F">Dim</SPAN> SHEET_1 <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> SHEET_2 <SPAN style="color:#00007F">As</SPAN> Worksheet
    
    <SPAN style="color:#00007F">Dim</SPAN> SHEET_1_lastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> SHEET_2_lastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    <SPAN style="color:#00007F">Dim</SPAN> csvDump <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    <SPAN style="color:#00007F">Dim</SPAN> errCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    <SPAN style="color:#00007F">Const</SPAN> DUMPFILE <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "C:\Temp\dump.csv"
    
    <SPAN style="color:#00007F">Set</SPAN> SHEET_1 = ActiveWorkbook.Sheets("Sheet1") <SPAN style="color:#007F00">'change to your sheet1</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> SHEET_2 = ActiveWorkbook.Sheets("Sheet2") <SPAN style="color:#007F00">'change to your sheet2</SPAN>
    
    SHEET_1_lastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
    SHEET_2_lastRow = Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Row
    
    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> SHEET_1_lastRow
        <SPAN style="color:#00007F">With</SPAN> SHEET_1
            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
            SalesPeople.Add Item:=Range2CSV(.Range(.Cells(i, 1), .Cells(i, 4))), _
                key:=CStr(.Cells(i, 1))
            <SPAN style="color:#00007F">If</SPAN> Err.Number <> 0 <SPAN style="color:#00007F">Then</SPAN>
                MsgBox "Salesperson ID: " & .Cells(i, 1) & vbNewLine & vbNewLine & _
                    "This ID already exists in the collection.", vbOKOnly + vbInformation, "Error"
                errCount = errCount + 1
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> SHEET_2_lastRow
        <SPAN style="color:#00007F">With</SPAN> SHEET_2
            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
            Sales.Add Item:=Range2CSV(.Range(.Cells(i, 1), .Cells(i, 4))), _
                key:=CStr(.Cells(i, 1))
            <SPAN style="color:#00007F">If</SPAN> Err.Number <> 0 <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">Dim</SPAN> tmp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
                
                tmp = Sales(CStr(.Cells(i, 1)))
                
                Sales.Remove <SPAN style="color:#00007F">CStr</SPAN>(.Cells(i, 1))
                
                Sales.Add Item:=tmp & "," & Range2CSV(.Range(.Cells(i, 1), .Cells(i, 4))), _
                    key:=CStr(.Cells(i, 1))
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
                
    csvDump = SalesPeople(1) & "," & Sales(Mid(SalesPeople(1), 2, 4))
                
    <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> SalesPeople.Count
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
        csvDump = csvDump & vbNewLine & _
            SalesPeople(i) & "," & Sales(Mid(SalesPeople(i), 2, 4))
        <SPAN style="color:#00007F">If</SPAN> Err.Number <> 0 <SPAN style="color:#00007F">Then</SPAN>
            csvDump = csvDump & vbNewLine & SalesPeople(i)
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
    <SPAN style="color:#00007F">Next</SPAN>
    
    f = FreeFile
    
    <SPAN style="color:#00007F">Open</SPAN> DUMPFILE <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Output</SPAN> <SPAN style="color:#00007F">As</SPAN> #f
        <SPAN style="color:#00007F">Print</SPAN> #f, csvDump
    <SPAN style="color:#00007F">Close</SPAN> #f

    MsgBox "Process Complete!" & vbNewLine & vbNewLine & _
        "Errors: " & errCount, vbOKOnly + vbInformation, "CSV Dump"

    <SPAN style="color:#00007F">Set</SPAN> SalesPeople = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> Sales = <SPAN style="color:#00007F">Nothing</SPAN>
    
    <SPAN style="color:#00007F">Set</SPAN> SHEET_1 = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> SHEET_2 = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Function</SPAN> Range2CSV(value <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> tmp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> value.Cells
        tmp = tmp & ",""" & c.value & """"
    <SPAN style="color:#00007F">Next</SPAN>
    
    Range2CSV = Mid(tmp, 2, Len(tmp) - 1)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>
 
Upvote 0
a little twist

the code works for the given data, but the in the final data my agent id is actually in the third column i-e C

column a --> Last name
column b --> First name
column c --> Salesperson ID
column d --> effective date
column e --> term date

thanks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA twist
Replies
5
Views
2K
abshaw
A
A
  • Question Question
!urgent! comma dilemited file export with null date fields
Replies
4
Views
1K
Ian Mac
I

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