asked on

Need macro to import parts of Purchase Order in custom csv format into excel (part 2)

Continuing from part 1 (https://www.experts-exchange.com/questions/27867545/Need-macro-to-import-parts-of-Purchase-Order-in-custom-csv-format-into-excel-Part-1.html)

After importing each csv file, macro will need to enter the data into a correct spreadsheet after comparing the "store number" value. (sample excel file included) Book1.xlsx
So for example, if a csv file contains store number 0524, it will match with spreadsheet called "Store 1" based on the G1 value in that spreadsheet.
The macro will then continue entering the weight of each product into a specific product after comparing the GTIN value.

aikimark

What do you want to call the sheets?

m888

ASKER

The sheets will be store 1, store 2, store 3 etc.. (as in the Book1.xlsx sample workbook)

aikimark

Where do those values come from?

m888

ASKER

The workbook already exist as well as the sheets name.
Obviously it won't be named store1, store2 etc but the correct store name <abbreviated> and without the store id.
That's why I need to check the value at G1 (or might be place anywhere else) against the store id from the csv file to make sure the imported data stored in the right sheet.

aikimark

>>and without the store id.
Are you saying that the store id will not be in G1? If so, I'm more confused.

What if there isn't a corresponding store value in any of the worksheets at G1?

m888

ASKER

I meant not specifically in G1, maybe F1 etc..

Although unlikely but if there's no corresponding store value then that specific csv file will not be moved to "processed" folder.

m888

ASKER

Thank you for taking the time on this problem.
Appreciate it!

aikimark

Here's an idea. upload a zip file containing 3-5 different vendor files and a workbook that contains a mixture of matching and not matching (or missing) store numbers.

m888

ASKER

Ok. Did that.

Made a zip including directory structure that I use.

I modified the workbook so it contains 4 stores.
And include 5 csv files with 4 matching store number and 1 non-matching store.

Btw, when moving the data to the matched sheet, can you also include the PO No on field C3?

Thanks again.
Puchase-Order.zip

aikimark

Thanks. Comments in the prior thread (part 1).

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

m888

ASKER

Working great!

The only problem is that if it's processed, it will be moved to

C:\Users\AikiMark\Downloads\Imported CSV\Processed\YYYY MM DD\ --> (today date)

if the directory doesn't exist it will create, but if it's exist just move the file.

aikimark

No where in your problem description, part1 or part 2, do you state this. Is this a new requirement?

m888

ASKER

I did mention it in part 1

"- Move the processed csv files into a subfolder called "Processed on "today date""

But i thought instead of "processed on today date" folder, it would be better to move it to directory imported csv >> processed >> today date.

But i think i should post this as new question? Let me know.

aikimark

Yes. You did. And you supplied a similarly named folder in your zip file.

But there was nothing to the effect that the program needs to create a folder with such a name if it doesn't exist. I assumed that you were creating the folder by some other process, manual or otherwise.

This version of the driver routine should come close to what you need.

Public Sub Q_27868780()
    'processes vendor files
    Const cVendorFilePath As String = "C:\Users\AikiMark\Downloads\Imported CSV\"
    Const cProcessedPath As String = "C:\Users\AikiMark\Downloads\Imported CSV\Processed on "
    Dim strProcessedPath As String
    Dim strFilename As String
    strProcessedPath = cProcessedPath & Format(Now, "yyyy_mm_dd")
    If Len(Dir(strProcessedPath, vbDirectory)) = 0 Then
        MkDir strProcessedPath
    End If
    strProcessedPath = strProcessedPath & "\"
        
    strFilename = Dir(cVendorFilePath & "*.txt")
    Do Until Len(strFilename) = 0
        If Q_27867545(cVendorFilePath & strFilename) = True Then
            'move to processed directory
            FileCopy cVendorFilePath & strFilename, strProcessedPath & strFilename
            Kill cVendorFilePath & strFilename
        End If
        strFilename = Dir
    Loop
End Sub

Open in new window

m888

ASKER

Thanks a lot for your help aikimark!

It's all works perfectly!