Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2012-09-18
16
Medium Priority
?
304 Views
Last Modified: 2012-09-25
Continuing from part 1 (http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27867545.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.
0
Comment
Question by:m888
  • 8
  • 8
16 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 38412215
What do you want to call the sheets?
0
 

Author Comment

by:m888
ID: 38412344
The sheets will be store 1, store 2, store 3 etc.. (as in the Book1.xlsx sample workbook)
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38412455
Where do those values come from?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:m888
ID: 38412685
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.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38413253
>>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?
0
 

Author Comment

by:m888
ID: 38413421
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.
0
 

Author Comment

by:m888
ID: 38413423
Thank you for taking the time on this problem.
Appreciate it!
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38413551
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.
0
 

Author Comment

by:m888
ID: 38413704
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
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38414891
Thanks.  Comments in the prior thread (part 1).
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 38432744
The part 1 code is now a boolean function that returns whether the store worksheet was found and processed.  It is called by Q_27868780() that iterates the txt files in the directory and moves them if processed.

Option Explicit

Public Function Q_27867545(ByVal parmFilePathName As String) As Boolean
    'Merges data from single vendor file into worksheet(s), based on G1 data = store number
    Dim intFN As Integer
    Dim strVendorData As String
    Dim strHeaderDetail() As String
    Dim lngLine As Long
    Dim strLineParsed() As String
    Dim wks As Worksheet
    Dim rng As Range
    Dim strPO As String
    Dim strStore As String
    Dim dicItems As Object
    
    Set dicItems = CreateObject("scripting.dictionary")
    
    intFN = FreeFile
    Open parmFilePathName For Input As #intFN
    strVendorData = Input(LOF(intFN), #intFN)
    Close #intFN
    
    strHeaderDetail = Split(strVendorData, "*POITEM,")
    strLineParsed = Split(strHeaderDetail(lngLine), ",")
    strPO = strLineParsed(1)
    strStore = strLineParsed(39)
    
    For lngLine = 1 To UBound(strHeaderDetail)
        strLineParsed = Split(strHeaderDetail(lngLine), ",")
        dicItems.Add strLineParsed(17), strLineParsed(5)
    Next
    
    Q_27867545 = False
    For Each wks In Workbooks("book1.xlsx").Worksheets
        If wks.Range("G1").Value = Val(strStore) Then
            Q_27867545 = True
            wks.Range("C3").Value = strPO
            For Each rng In wks.Range(wks.Range("A6"), wks.Range("A6").End(xlDown)).Cells
                If dicItems.exists(rng.Text) Then
                    rng.Offset(0, 3).Value = dicItems(rng.Text)
                End If
            Next
            Exit For
        End If
    Next
    dicItems.RemoveAll
    Set dicItems = Nothing
End Function

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 2012_09_20\"
    Dim strFilename As String
        
    strFilename = Dir(cVendorFilePath & "*.txt")
    Do Until Len(strFilename) = 0
        If Q_27867545(cVendorFilePath & strFilename) = True Then
            'move to processed directory
            FileCopy cVendorFilePath & strFilename, cProcessedPath & strFilename
            Kill cVendorFilePath & strFilename
        End If
        strFilename = Dir
    Loop
End Sub

Open in new window

0
 

Author Comment

by:m888
ID: 38435221
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.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38435283
No where in your problem description, part1 or part 2, do you state this.  Is this a new requirement?
0
 

Author Comment

by:m888
ID: 38435341
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.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38435359
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

0
 

Author Comment

by:m888
ID: 38435451
Thanks a lot for your help aikimark!

It's all works perfectly!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question