Loading content of two text files into columns A and B with VBA-code, each item in the files in a separate cell in column A and column B

[CasperA]

I have a list of suppliers of car components in a text file, called Suppliers.txt, in the path C:\Suppliers\Suppliers.txt, and a list of names of cars, also in a text file, in the path name C:\Cars\Cars.txt.

The file [Suppliers.txt] contains names of suppliers separated by a hard return

supplier 1 [hard return]
supplier 2 [hard return]
etc.

The file [Cars.txt] contains names of cars separated by a hard return

Volvo [hard return]
BMW [hard return]
etc.

Is it possible to load these two lists with a VBA-macro into columns A and B of an Excel-spreadsheet, each item in the files ending up in a separate cell in column A and column B?

Thank you in advance,
CA

 

[igold]

Hi CA,

Does this do what you want it to?

Sub Cars_Suppliers()

    Dim LineFromFile As Variant
    Dim FilePath(2) As String
    Dim rCnt As Long
    Dim iCnt As Integer
    
    For iCnt = 1 To 2
        FilePath(1) = "C:\Suppliers\Suppliers.txt"
        FilePath(2) = "C:\Cars\Cars.txt"
        Close #1
        Close #2
        Open FilePath(iCnt) For Input As #iCnt
    
        rCnt = 1
        Do Until EOF(iCnt)
            Line Input #iCnt, LineFromFile
            Dim LineItems As Variant: LineItems = Split(LineFromFile, vbCrLf)
            Cells(rCnt, iCnt).Value = LineItems(0)
            rCnt = rCnt + 1
        Loop

        Close #iCnt
    Next
    
    Cells.Columns.AutoFit
End Sub

HTH

igold

 

[CasperA]

Hi CA,

Does this do what you want it to?

Sub Cars_Suppliers()

    Dim LineFromFile As Variant
    Dim FilePath(2) As String
    Dim rCnt As Long
    Dim iCnt As Integer
    
    For iCnt = 1 To 2
        FilePath(1) = "C:\Suppliers\Suppliers.txt"
        FilePath(2) = "C:\Cars\Cars.txt"
        Close #1
        Close #2
        Open FilePath(iCnt) For Input As #iCnt
    
        rCnt = 1
        Do Until EOF(iCnt)
            Line Input #iCnt, LineFromFile
            Dim LineItems As Variant: LineItems = Split(LineFromFile, vbCrLf)
            Cells(rCnt, iCnt).Value = LineItems(0)
            rCnt = rCnt + 1
        Loop

        Close #iCnt
    Next
    
    Cells.Columns.AutoFit
End Sub

HTH

igold

Hello igold, thank you, that works perfect!

CA

 

[igold]

That's great. Glad I could help. Thanks for the feedback!

igold