VBA required to speed up .csv import

[cjcass]

I carry out the following actions many times, is there some code I can use to speed up the process? I’m essentially importing data from a .csv file that is located in my Downloads folder - it’s a different file every time but I’m going through the same process, as follows:

1. Select 'Data' then 'From Text'
2. I then select a .csv file from the folder located at 'C:\Users\Peter\Downloads' and select ‘Import’ (I need to select the file manually)
3. Then select ‘Delimited’ and ‘65001 : Unicode (UTF-8)’, then ‘Next’
4. Then select ‘Comma’ and ‘Next’
5. The ‘Finish’ and ‘=$A$1’

So, I'm looking for a two part solution where I execute the macro and it takes me to the Downloads folder where I select the file and the second part then runs.
Hopefully this makes sense, any help much appreciated.

 

[dave3009]

Assuming you're doing this using Power Query, just ensure the filename is the same and refresh the query.

 

[cjcass]

Assuming you're doing this using Power Query, just ensure the filename is the same and refresh the query.

Hi, I've never used Power Query, am using Excel 2016

 

[Joe4]

If you turn on the Macro Recorder, and record yourself performing these steps manually, you will get most of the VBA code you need in order to do this automatically.
The only part that you won't get the file file select.

There are a lot of posts on this site which show you how you can have VBA bring up a file browser to select the file you want to import.
This Google search shows many of them: Google Search

If you run into any issues incorporating this, please post the code you came up with along with your questions here to this thread.

 

[cjcass]

I have tried recording a macro but it only gives me the second part of the requirement (steps 3-5 above), my problem is being able to execute the first two steps by vba (1-2 above) and when the file is selected automatically run the second bit of code. My code is as below, but I don't want a hard coded file (eg. 'translation_pa (6).csv'), any further help with this code appreciated.

Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Peter\Downloads\translation_pa (6).csv", Destination:=Range( _
        "$A$1"))
        .CommandType = 0
        .Name = "translation_pa (6)"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("A1").Select
End Sub

 

[Joe4]

I have tried recording a macro but it only gives me the second part of the requirement (steps 3-5 above), my problem is being able to execute the first two steps by vba (1-2 above) and when the file is selected automatically run the second bit of code. My code is as below, but I don't want a hard coded file (eg. 'translation_pa (6).csv'), any further help with this code appreciated.

Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Peter\Downloads\translation_pa (6).csv", Destination:=Range( _
        "$A$1"))
        .CommandType = 0
        .Name = "translation_pa (6)"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("A1").Select
End Sub

Did you not look at the link I provided that shows old threads that show you how to do the file browse part?

 

[cjcass]

Did you not look at the link I provided that shows old threads that show you how to do the file browse part?

Yes, but I am on here because I have already scanned the web to try and come up with a solution, to no avail - hence me being on here asking for help.

 

[Joe4]

It looks like the "OpenWorkbook" procedure in this link here has code that does what you want - it opens a File Browser where you can specifiy which folder it will open to, and you can filter the types of files it shows. Using File and Folder Dialog Boxes in VBA