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.

 

[Zot]

Probably it is easier to just import everything is a temporary sheet. Then do comparison the extract data needed into another final sheet. Then just delete that temporary sheet. So, at the end you just see your required data.

 

[josros60]

Probably it is easier to just import everything is a temporary sheet. Then do comparison the extract data needed into another final sheet. Then just delete that temporary sheet. So, at the end you just see your required data.

Thank you for responding.

I'm not that good in that would you mind giving an idea or example how to, much appreciated.

 

[Zot]

Thank you for responding.

I'm not that good in that would you mind giving an idea or example how to, much appreciated.

I thought the code you provided is used to import csv file, right?
1) Import csv file using your code
2) Add another worksheet
3) Write macro go through the list in imported csv file and copy the one that match the Vendor ID to newly created sheet.
4) Once all is done, just delete the imported csv file from the workbook using macro.

So, you will have the data you required. Since you are able to write the code above, I'm sure this step is not a problem to you.

 

[josros60]

Thank you.

No that code found it online and change cells.

Have no idea how to do your point #3 writing the macro, any suggestions?

Thanks again

 

[Zot]

Perhaps several helpers will suggest their codes for the problem. However, it can be confusing cause most likely everyone has their own imaginary worksheet.

It is best that you give sample of how your csv looks like and how you want you result to look like. I suggest that you use XL2BB to copy paste the sheet. Otherwise you can upload to drop box and any cloud storage that helpers can start work on it.

 

[josros60]

Here template sample:

ACCRUALS.xlsm
	A	B	C	D
1	SAGEID	VENDOR	INV DATE	AMOUNT
2	100215	ABC Communications
3	100215	ABC Communications
4	100215	ABC Communications
5	100128	Abstractive Technologies Consulting
6	100109	AFX Communications
7	100176	AIRESPRING
8	100053	Allstream
9	100053	Allstream
10	100053	Allstream
11	100053	Allstream
12	100053	Allstream
13	100053	Allstream
14	100072	ANI Networks
15	100013	Anixter Canada
16	100226	ARIN
17	100067	AT&T DG (1548) SNET
18	100125	AT&T Diversified Group
19	100125	AT&T Diversified Group
20	100125	AT&T Diversified Group
21	100045	BC Hydro Consolidated account
22	100286	BC One Call Limited
23	100384	BEANFIELD METROCONNECT
NCL_ACCRUALS

 

[Zot]

I was quite busy today. This probably did not meet your exact need.

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 = Application.GetOpenFilename("csv Files(*.csv),*.csv", , "Please select a file", MultiSelect:=False)
    
If FName = False Then                                                        'CANCEL is clicked
    Exit Sub
End If
' Define opened csv file as wbSource
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]

Thank you very much for the response.

I pasted your code and modify it little bit but getting error 1004 Application defined or object-defined error

and highlight this line:

' 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) & "1", wsSource.Cells(Rows.Count, ArryColSource(0)).End(xlUp))

Complete code:

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 = Application.GetOpenFilename("csv Files(*.csv),*.csv", , "Please select a file", MultiSelect:=False)
    
If FName = False Then                                                        'CANCEL is clicked
    Exit Sub
End If
' Define opened csv file as wbSource
Set wbSource = Workbooks.Open(Filename:=FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set wsSource = wbSource.Sheets("NCL_ACCRUALS")                            ' 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 = "A2,B2,C2,D2,E2,F2"
strColSource = "CF,CG,DM,DQ,DU,DL"

' 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) & "1", 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

thanks again

 

[Zot]

Here should be column alphabet, not cell range
strColMaster = "A2,B2,C2,D2,E2,F2"

Change to this
strColMaster = "A,B,C,D,E,F"

Row 2 is defined here
Set rngVendorIDMaster = wsMaster.Range(ArryColMaster(0) & "2", wsMaster.Cells(Rows.Count, ArryColMaster(0)).End(xlUp))