How to combine multiple spreadsheets into one, part 3

Harry Lee helped me with this awesome code but i need to make a little tweak.  The script is looking for a .xlsx. I need it to look for a .csv. I went into the code and saw .xlsx twice. I changed it to csv but it didn't work. How would we change it to look for .csv? I attached the spreadsheet with the macro. Thanks,
SO.xlsx
brasimanAsked:
Who is Participating?
 
Harry LeeCommented:
brasiman,

This contains both the xlsx and csv version.

Harry
SOImport.xlsm
0
 
gowflowCommented:
The file that you posted is an .xlsx and can't have any code. the file that have a code is macro enabled file either and .xls (earlier versions) or .xlsm

If you need help with the script either post the correct file or post the code.
gowflow
0
 
brasimanAuthor Commented:
Thanks gowflow. Sorry about that. Good call. I attached the wrong one. Here it is...
SOImport.xlsm
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
gowflowCommented:
ok try this does it solve your problem ?
gowflow
SOImport-V01.xlsm
0
 
brasimanAuthor Commented:
It kind of worked. It didn't go through all of my orders. It stopped on the first one and an error popped up. I attached screenshots of the errors.
Errors.docx
0
 
gowflowCommented:
We are playing hide and seek. Pls post the csv file so I test your code. I simply took your work for it and changed the xlsx to csv so the macro could work but obviously nothing was tested.

So if you want a workable solution kindly attach the csv file
gowflow
0
 
brasimanAuthor Commented:
I attached the .csv. Thank you.
SO.csv
0
 
gowflowCommented:
Well it worked perfectly here. Don't understand why you got this error do you have multiple csv ? and they should all be in the same folder right ?

Anyway try this version and you can see it imported the data correctly.
gowflow
SOImport-V02.xlsm
0
 
brasimanAuthor Commented:
Odd. Yes I have multiple but they are all the same. You could just copy and rename the same file so you have multiple to check on your end if multiple .csv's work. I'll try the v02 and see if that works.
0
 
brasimanAuthor Commented:
Ok so that worked! However, now it is pulling in "Order Total" as a line item, and its only supposed to pull in everything that has a qty in column A. So if you pull up the SO.csv i attached, D17 you will see "Order Total:". That is showing up as a line item on the SOImport. What do we need to adjust so it only looks at the rows that have a quantity? Is the one you are trying pulling the part number from column C or G? I don't know how to read the code. :) I need it to pull from column G, the Comment column.
0
 
brasimanAuthor Commented:
I probably didn't explain that well. Sorry. I modified the code to look at column G for the part, not column C. After i did that, the "Order Total:" on the SO.csv started showing up as a line item on the SOImport.
0
 
brasimanAuthor Commented:
Actually, i just noticed its on yours too. Look at column K on SOImport-V02.xlsm. That wasn't originally there.
0
 
gowflowCommented:
ok fine

what do you need ? exactly please state in detail as the code is too amature.

When I get back will look at all this.
gowflow
0
 
gowflowCommented:
Here it is by adding a line of total it consider that the cells are used so I used Col G to pick up last used row.

Chk this version
gowflow
SOImport-V03.xlsm
0
 
brasimanAuthor Commented:
Thanks!

This is what I am trying to do:

I have Sales Orders from customers that come in Excel format. Every SO is in the exact same format. The only difference is the SO # and the line items on the SO (qty, part and price). So there are two parts to this. The first is the  SOImport spreadsheet. The 2nd part are the individual SO's, SO-1, SO-2, SO-3, etc. We are then taking the individual SO's (SO-1, SO-2, etc) and manually putting them in the spreadsheet, "SOImport". We then take the SOImport spreadsheet and import it into our software.

I am trying to figure out a way we can put these individual SO's in a folder, run a script and have the SO#, part #, qty and price from all the SO's populate on the SOImport spreadsheet. I also need a way the SO Numbers can automatically be created when pushed to to the SOImport spreadsheet.

This current code works, except the "Order Total:" is showing up on the SOImport after the macro is run. If there is a way to keep the code as is, but tweak it so it says if column A is greater than 0, take the part number in the Comment column (G), Unit Price (column D) and quantity (column A) and move them to the SOImport.xlsm. Here's the mapping:
SO Column A (Qty) to SOImport Column J
SO Column D (Unit Price) to SOImport Column K
SO Column G (Comment) to SOImport Column E.

There are going to be multiple SO's. So, the macro should compile all those and just populate the SOImport. The very first one that I was helped with did exactly what I am looking for, but we made one tweak and i'm not sure what happened. Because now the "Order Total:" shows up on the SOImport when it is run. I attached the original.
SOImport--4-.xlsm
0
 
brasimanAuthor Commented:
Ok so the one you sent worked. Except when i put the next SO number in A15, those aren't populating like they were. The way is should work is I put the order number in A15, run the macro, then the SO numbers start with what i have in A15.
0
 
Harry LeeCommented:
brasiman,

All you need to do to pickup CSV instead of XLSX, is to basically change those two lines that contain the XLSX.

You did the first step correctly by changing the 2 instances of XLSX to CSV.

The only thing you missed is the 2rd instance is a Right formula. You have to change the number of character from 4 to 3.

FROM
'   Get SOs folder from user
    strPath = GetFolder("C:\") & "\"
    strFile = Dir(strPath & "*.xlsx")

Open in new window

TO
'   Get SOs folder from user
    strPath = GetFolder("C:\") & "\"
    strFile = Dir(strPath & "*.csv")

Open in new window

and

FROM
'   Loop through all files in user specified folder tor workbooks
    Do While strFile <> ""
        If Right(strFile, 4) = "xlsx" Then
            Set swbk = Workbooks.Open(Filename:=strPath & strFile)
            Set sws = ActiveSheet

Open in new window

TO
'   Loop through all files in user specified folder tor workbooks
    Do While strFile <> ""
        If Right(strFile, 3) = "csv" Then
            Set swbk = Workbooks.Open(Filename:=strPath & strFile)
            Set sws = ActiveSheet

Open in new window

0
 
Harry LeeCommented:
brasiman,

I have updated the code to pickup CSV instead of XLSX. I have also incorporated the check for the single space in column A to avoid picking up the Total line.

Please check.

Please also make sure you don't delete off the Function, and please only replace the Sub.
Sub ImportSOsCSV()
'--------------------------------------------------
'   Declare Variables
    Dim strPath As String, strFile As String
    Dim swbk As Workbook, twbk As Workbook
    Dim tws As Worksheet, sws As Worksheet
    Dim twsn As String
    Dim CurRw As Long, I As Long, SOLstRw As Long
    

'--------------------------------------------------
'   Set twbk Object Variables Values
    Set twbk = ActiveWorkbook

'--------------------------------------------------
'   Create new sheet naming using date and time

'    twbk.Sheets.Add.Name = Format(Now, "yyyymmddhhmmss") & "SOImport"
'    Set tws = ActiveSheet
'    tws.Cells(1, 1) = "(blank)"
'    tws.Cells(1, 2) = "Order#"
'    tws.Cells(1, 3) = "(blank)"
'    tws.Cells(1, 4) = "(blank)"
'    tws.Cells(1, 5) = "ItemNo"
'    tws.Cells(1, 6) = "(blank)"
'    tws.Cells(1, 7) = "(blank)"
'    tws.Cells(1, 8) = "(blank)"
'    tws.Cells(1, 9) = "(blank)"
'    tws.Cells(1, 10) = "OrderQty"
'    tws.Cells(1, 11) = "EAPrice"
'    tws.Cells(1, 13) = "Import Ref"
'    tws.Rows(1).Font.Bold = True
    
'--------------------------------------------------
'   Use existing sheet POOrderLine as the target sheet
    Set tws = twbk.Sheets("POOrderLine")
    
'--------------------------------------------------
'   Removing old data from POOrderLine sheet
    If Len(Cells(2, 2)) <> 0 Then
        tws.Range(Rows(2), Rows(Cells(Rows.Count, 2).End(xlUp).Row)).EntireRow.Delete
    End If
'--------------------------------------------------
'   Get SOs folder from user
    strPath = GetFolder("C:\") & "\"
    strFile = Dir(strPath & "*.csv")
    
    
'--------------------------------------------------
'   Define current working row
    CurRw = 2
    
'--------------------------------------------------
'   Loop through all files in user specified folder tor workbooks
    Do While strFile <> ""
        If Right(strFile, 3) = "csv" Then
            Set swbk = Workbooks.Open(Filename:=strPath & strFile)
            Set sws = ActiveSheet

'--------------------------------------------------
'   Find the Last Valid Row of SO Detail
    SOLstRw = sws.Cells(Rows.Count, 1).End(xlUp).Row
    If Len(Application.WorksheetFunction.Substitute(sws.Cells(SOLstRw, 1), " ", "")) = 0 Then
    SOLstRw = SOLstRw - 1
    End If

'--------------------------------------------------
'   Loop through all detail lines of SO files and copy onto POOrderLine sheet
                For I = 10 To SOLstRw
                    tws.Cells(CurRw, 2) = tws.Cells(1, 15)
'                    tws.Cells(CurRw, 5) = sws.Cells(I, 3) 'Old location of Part Number
                    tws.Cells(CurRw, 5) = sws.Cells(I, 7) 'New location of Part Number
                    tws.Cells(CurRw, 10) = sws.Cells(I, 1)
                    tws.Cells(CurRw, 11) = sws.Cells(I, 4)
                    tws.Cells(CurRw, 11).NumberFormat = "$#,##0.00_);($#,##0.00)"
                    tws.Cells(CurRw, 13) = "From " & strFile
                    CurRw = CurRw + 1
                Next
            
'--------------------------------------------------
'   Close SO file
            swbk.Close SaveChanges:=False

'--------------------------------------------------
'   Increase Next Order number stored in cell O1 by 1
            tws.Cells(1, 15) = tws.Cells(1, 15) + 1
        End If
        
'--------------------------------------------------
'   Resume Loop
        strFile = Dir
    Loop

'--------------------------------------------------
'   Remove Object Varibles from memory
Set twbk = Nothing
Set tws = Nothing
End Sub

Open in new window

0
 
Harry LeeCommented:
brasiman,

Also, just a reminder. The Next SO number is not in A15. The line Cells(1, 15) means the 15th column, and 1st row, which is O1.

The typical reference type Range("O1") has Column goes first then Row. The reference I use Cells, is the other way around. Cells( Row#, Col#).
0
 
brasimanAuthor Commented:
Thank you!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.