Import CSV file into template

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
635
Office Version
  1. 365
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:

VBA Code:
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
635
Office Version
  1. 365
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,881
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,881
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Rich (BB code):
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

Well-known Member
Joined
Jun 27, 2010
Messages
635
Office Version
  1. 365
Rich (BB code):
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,881
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.

Rich (BB 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 = "<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
 
Solution

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
635
Office Version
  1. 365
Sorry my mistake was pasting the wrong file.

worked perfectly.

thank you so much for all your great help.
 

Forum statistics

Threads
1,148,242
Messages
5,745,588
Members
423,963
Latest member
lwilson3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top