VBA: Excel Vlookup Referencing a Closed CSV file

Question

I have an excel file where I delete unnecessary columns, insert a new row and label the columns I have left. What I would like to do now is write a VBA code that will go into a closed excel csv, look for and pull the desired information. I have previously tried How to access a closed Excel Workbook using vlookup vba , but that code didn't work. So I tried the code below

Sub TestP2P()
 Range("A:AB,AE:AU,AX:DM").delete
 Range("A1").EntireRow.Insert
 Range("A1").Value="Name"
 Range("B1").Value="Description"
 Range("C1").Value="Price"
 Range("D1").Value="Customer"
 Range("E1").Value="Type"
 Range("E2").Select
 ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-4],Book1.csv!R1C16:R2257C23,8,FALSE)"
 Range("E3").Select
 End Sub

This code didn't work either it just returned a blank cell. I also tried:

Public Sub Example()
Dim Path As String

Path = "C:Desktop\Test\"

With ThisWorkbook.Sheets("Sheet1")
    .Range("E2").Formula = "=VLOOKUP(A2,'" & Path & "[WorkbookName]'!P:W,8,FALSE)"
End With
End Sub

I just received an error on my file path. I'm confused on how to go about using the Vlookup function within vba and would appreciate any advice on how to write the code.

Erik E. Flores Delfín Erik E. Flores Delfín · Accepted Answer · 2019-07-25T19:41:00

As I remember the csv file represents another format in excel that do not fit with the "Queries & Connections" options from Excel, so indeed by using the CSV example and press Check Status the applicaiton cannot know what type of ile it is.

The Status shows 'Unknown'. What I recommend is to save the csv file a xlsx format, or in the code please instead of using .Formula="something" please read the csv as an external file n the code and use the split methods to create array that can be looped in orde to get the info cell by cell.

VBA: Excel Vlookup Referencing a Closed CSV file

1 Answers