Is there a way to import data from CSV file to xlsx table using VB?

I currently have a csv file and xslm file in the same folder, and right now, I have to open the CSV, copy column A to a CSV tab on my xlsm file, then I have a VB script that is activated by a button that does text to columns and removes duplicates. I then have a formula pulling that information in to another worksheet to populate the AP review table. I would like to remove the step of opening the CSV file altogether, if possible. I would like to basically combine the code from my csv button with something that can import the data. I am extremely new to programming with VB, so any help is appreciated.

Is there a way to use a VB script to take the data from column A of the CSV file, strip away everything except the 3 digit number, remove the duplicates, and input the data in column A of my table in my xlsx file?

There will only be one csv file in the folder, so *.csv could be used to call that file.

Here is a sample of the data in column A in the CSV file:

001 CoordX -350
001 CoordY 31
001 APModel
002 CoordX -365
002 CoordY 102
002 APModel
003 CoordX -315
003 CoordY 280
003 APModel
004 CoordX -227
004 CoordY 278
004 APModel
005 CoordX -139
005 CoordY 283
005 APModel
006 CoordX -75
006 CoordY 258
006 APModel
007 CoordX -14
007 CoordY 286
007 APModel

I would like to use a button in the xlsx file to grab the data from column A in the CSV file, convert the text to columns, strip away everything except the 3 digit number, remove the duplicates, and input the data in column A of my table in my xlsx file?

I don't want to change anything in any other columns.

The input destination is A202 on the xlsx file.

When I push the button, I'd like to activate the vb script to do the above and have column A of my table look like this:

AP001
AP002
AP003
AP004
AP005
AP006
AP007


Please let me know if I can provide any more details.

I have attached a sample CSV and xlsm files.

Thank you for your help!

Try this as an example:

Please Login or Register  to view this content.

Thank you for your reply!

Is there a way to either have the csv directory be limited to the folder that the xlsm and csv file are in or to have the user choose the csv file to import?

Our file structure is as follows:

C:\Users\%username%\OneDrive\Survey Review Reports\%Filename%\*.csv

%Filename% = (5 digit site ID.2 character country code)
01234.US, 00100.US, etc...

The CSV file will always be in the same directory as the xlsm file that the macro is being ran on.
The CSV file will always be named 01234.US, 05520.US, 01032.CA, etc... (5 digit site ID.2 character country code) The folder that contains these files is named the same (5 digit site ID.2 character country code).

Please Login or Register  to view this content.

I tried editing the macro to include that wildcard directory, but I'm afraid I don't know enough about it yet and I'm getting an error on "Open PathAndFileName For Binary As #FileNum"

Please Login or Register  to view this content.

Hi all. Perhaps replace Const PathAndFileName As ... with:

Please Login or Register  to view this content.

This assumes the intended source is the only .csv file in that directory, as stated in post#1.

Or this... Similar to post#2, but uses the post#4 pathname hack, a dictionary for duplicate removal, and table references for output:

Please Login or Register  to view this content.

What Div you are using?