CODEWITHSUNDEEP
excelvba
Toli2017
Toli2017

Reputation: 47

Open any file using Excel Macros

So I have this code to load data from .csv files into excel sheet. I recorded macros but how can I make user select custom files every time macros executed?

 Range("B11").Select
With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\Users\admin\Desktop\VAT\te\VAT_SALES_201801.csv", Destination:=Range _
    ("$B$11"))
    .Name = "VAT_SALES_201801"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 1252
    .TextFileStartRow = 3
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = True
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 9, 1, 9, 1, 1, 1, 1, 9, 9, 9, 9, 9)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

I do not want the file part to be hardcoded.

Upvotes: 1

Views: 92

Answers (1)

Davesexcel
Davesexcel

Reputation: 6982

        Sub Button1_Click()
            fileToOpen = Application _
                         .GetOpenFilename("csv Files (*.csv), *.csv")
            If fileToOpen <> False Then
                With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & FileToOpen, Destination:=Range _
            ("$B$11"))
            .Name = "VAT_SALES_201801"
'...

'...

            End If
        End Sub

Upvotes: 2

Related Questions