Import CSV file into template

[josros60]

Hi,

Vendor spreadsheet with the following fields:

VendorID Vendor Name and Amount

is it possible to import a csv file that will import the amounts to match vendor using the VendorID, I use the code below to import others but no sure it can be modify to process the above.

this to do list of accruals meaning if there's no amount the vendor with zero amount will be the one to accrue.

here is the code if possible to modify:

Sub ImportRawData()
 
    Dim c           As Long
    Dim Col         As Variant
    Dim Filename    As String
    Dim Filepath    As Variant
    Dim rngBeg      As Range
    Dim rngEnd      As Range
    Dim rngDst      As Range
    Dim rngSrc      As Range
    Dim rowsize     As Long
    Dim wkbDst      As Workbook
    Dim wkbSrc      As Workbook
    Dim vFile
    
    vFile = Application.GetOpenFilename("CSV Files(*.csv),*.csv", , "please select a file", MultiSelect:=False)
    
    
        Set wkbDst = ThisWorkbook
        Set rngDst = wkbDst.Worksheets("EFT Summary").Range("A5:H5")
        
        Filepath = "C:\Users\jose.rossi\Desktop\NCL EFT_Summary.xlsm"
        Filename = "apcbtclz.csv"
        
        On Error Resume Next
            Set wkbSrc = Workbooks(Filename)
            If Err = 9 Then
                If Filepath <> "" Then ChDir Filepath Else ChDir ThisWorkbook.Path
                'Filename = Application.GetOpenFilename("Excel Workbooks, *.xlsx")
                If Filename = "False" Then Exit Sub
                Set wkbSrc = Workbooks.Open(Filename)
            End If
        On Error GoTo 0
        
        ' Clear previous data.
        'rngDst.Resize(rngDst.Parent.UsedRange.Rows.Count).ClearContents
        
        ' Import the data.
        With wkbSrc.Worksheets("apcbtclz").UsedRange
            ' Step through the source data columns.
            For Each Col In Array("AW", "BO", "BB", "AX", "X", "CH")
            
                        
            
            
            
                ' Data starts on row 1.
                Set rngBeg = .Parent.Cells(1, Col)
                
                ' Find the row where the data ends in this column.
                Set rngEnd = .Parent.Cells(Rows.Count, Col).End(xlUp)
                
                ' Number of rows in this column.
                rowsize = rngEnd.Row '- rngBeg.Row
                
                If rowsize > 0 Then
                    Set rngSrc = .Parent.Range(rngBeg, rngEnd)
                    rngDst.Offset(0, c).Resize(rowsize, 1).Value = rngSrc.Value
                End If
                
                ' Increment the column offset.
                c = c + 1
                
                If c = 6 Then Let c = 7
            Next Col
            
        End With
        
End Sub

thank you.

 

[josros60]

Thank you so much, worked perfectly.

 

[josros60]

Hi,
Sorry last question for sure if you don't mind the importing file is in same location always how to put he path of the file in the code.

Thanks so much.

 

[Zot]

Hi,
Sorry last question for sure if you don't mind the importing file is in same location always how to put he path of the file in the code.

Thanks so much.

Here is the scenario that you want to consider. Say you keep adding source files in same folder, how do you want to let the program knows which file to choose? Therefore, you can only put a file in the folder and let the program open the only file in the folder and once done, move the folder to another folder. Well, this approach also need to another action to be performed.

Maybe that would be just my poor guessing of what you were trying to do. Please explain more.

 

[josros60]

I export same file every week at same location.

 

[Zot]

FName = Application.GetOpenFilename("csv Files(*.csv),*.csv", , "Please select a file", MultiSelect:=False) Put filename here with complete path.
   
If FName = False Then                                                        'CANCEL is clicked
    Exit Sub
End If

Try if above works

 

[josros60]

FName = Application.GetOpenFilename("csv Files(*.csv),*.csv", , "Please select a file", MultiSelect:=False) Put filename here with complete path.
  
If FName = False Then                                                        'CANCEL is clicked
    Exit Sub
End If

Try if above works

thank for the suggestion, but doesn't work. give me error 91

 

[Zot]

thank for the suggestion, but doesn't work. give me error 91

You said I export same file every week at same location.

The file name there is always the same name? If you hard-coded the name then it has to be the same all the time.

Just now I tried the method I proposed and it works just fine.

Sub ImportCSVData()

Dim n As Long
Dim cell As Range, rngVendorIDMaster As Range
Dim rngFound As Range, rngVendorIDSource As Range
Dim strColMaster As String, strColSource As String
Dim ArryColMaster() As String, ArryColSource() As String
Dim wsMaster As Worksheet, wsSource As Worksheet
Dim wbMaster As Workbook, wbSource As Workbook
Dim FName As Variant

Set wbMaster = ActiveWorkbook                                       ' This is the wb Accruals
Set wsMaster = wbMaster.Sheets("NCL_ACCRUALS")

FName = "<complete path here>\Book1.csv" 

Set wbSource = Workbooks.Open(Filename:=FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set wsSource = wbSource.Sheets("Book1")                            ' Change if necessary

' Since I have no idea how the imported csv file looks like, I just define the column here
' If all csv file columns of interest are the same then no problem (assuming column D, G, H, J as source columns here as example).

' Define matching column here correspondingly (import Inv No and Amount in this case)
strColMaster = "A,C,D"
strColSource = "A,H,J"

' Fill array with column letters
ArryColMaster = Split(strColMaster, ",")
ArryColSource = Split(strColSource, ",")

' Define range Vendor ID in wsMaster and wsSource. Assuming data in wsSource starts from row 2
Set rngVendorIDMaster = wsMaster.Range(ArryColMaster(0) & "2", wsMaster.Cells(Rows.Count, ArryColMaster(0)).End(xlUp))
Set rngVendorIDSource = wsSource.Range(ArryColSource(0) & "2", wsSource.Cells(Rows.Count, ArryColSource(0)).End(xlUp))

For Each cell In rngVendorIDMaster
    Set rngFound = rngVendorIDSource.Find(What:=cell.Value, LookAt:=xlWhole)
    If Not rngFound Is Nothing Then
        For n = 1 To UBound(ArryColMaster)
            wsMaster.Range(ArryColMaster(n) & cell.Row) = wsSource.Range(ArryColSource(n) & rngFound.Row)
        Next
    End If
Next

' Close wbSource without saving
wbSource.Close False

End Sub

 

[josros60]

Sorry my mistake was pasting the wrong file.

worked perfectly.

thank you so much for all your great help.

 

[Zot]

Sorry my mistake was pasting the wrong file.

worked perfectly.

thank you so much for all your great help.

Thanks for update.