How do I use VBA to import a .csv file into a worksheet?

0

Hello,

I am trying to write some VBA code that will allow a user to import data from a .csv or .txt file into a worksheet.  I've gone through a handful of the tutorials on this site and have a solid understanding on how to do this manually, but instead of teaching a handful of my employees this method (which would open the process to missed steps) I'd like to automate the import with the push of a button on the worksheet.  

A few details:

-The user should be prompted to choose the specific file they would like to import data from in a directory.

-The file names are less than consitent with file names like this:

CTI_0106446LHA9X3F9_2020-01-23_083943.csv

CTI_01064GY6DBRFF78_2020-01-24_122704.csv

The CTI in the beginning and timestamp format at the end are pretty consistent, but the string in the middle seems to be randomly generated (not sure if that matters, but more info can be better than less, right?)

-During the import process, the file types are delimited with commas (not tabs)

-I'd like to not save query definitions as the data is very basic and will not be modified after it is generated. (Probably not a huge detail, but if possible I'd like this feature).

This macro is coupling with another in that I am setting up a system where the user can import data onto an "Import1" worksheet and then send it to another "Set1" worksheet where it is transfered into a form that can be saved as a PDF.  Right now the user is entering all of the data manually from field data sheets and we are transistioning to a system where the field data will be entered digitally and automatically uploaded to our servers as a .csv file.  Just for some "big picture" context.

So, any code-starter-seeds or input would be appreciated.  Thank you in advance!

Answer
Discuss

Answers

0
Selected Answer

This site teaches Excel. It doesn't offer code-writing services, not even if paid.

With that said, I don't think you need VBA immediately. Teach your employees how to open a CSV or TXT file in Excel.

By default, the File Open dialog box displays "All Excel Files". Click on the selector with this caption at the lower right and change it to "All files". Select the CSV or TXT file you wish to open and click "Open".

Quite possibly this process will lead to questions which you can ask here. Quite possibly, given your limited trust in the Excel-prowess of your employees, the answers to those questions will lead to the suggestion to deploy VBA. The point is that the suggested approach will lead to very different problems, therefore very different questions and answers and, hence, very different VBA. 

Discuss
0

Just in case anyone in the future has a similar question, this is my VBA code that answered my query.


Sub Import_Data()
 
Dim FileToOpen As Variant
Dim ImportLocation As Range
Dim xAddress As String
 
FileToOpen = Application.GetOpenFilename(Title:="Browse for the Data File", FileFilter:="CSV Files (*.csv*),*csv*")
 
    If FileToOpen = False Then Exit Sub
    
        Set ImportLocation = Application.Range("A1")
       
        If ImportLocation Is Nothing Then Exit Sub
        
        xAddress = ImportLocation.Address
        
         With ActiveSheet.QueryTables.Add("TEXT;" & FileToOpen, Range(xAddress))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 936
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
        
 
End Sub
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login