Macro to import CSV file into excel inactive sheet

Question

I have an excel macro-enabled workbook that contains several named worksheets. One of the worksheets is called the “panel”, and the second worksheet is the “data”. On the sheet with the name "panel" there is a button to which a macro is assigned. I would like to select a button on a sheet with the name "panel" and open a window with a file. When the user selects the csv file on his hard drive, I would like the contents of the csv file to be imported into the worksheet with the name "data", starting from cell A1.

PROBLEM 1: the vba assigned to the button causes the contents of the csv file to be placed on the same worksheet as the button (worksheet "panel"). I would like the contents of the csv file to fit on the data sheet.

PROBLEM 2: There is also a line of code linking to my hard drive and a file called "capture.csv". Therefore, when the excel macro file is on another computer, the file crashes. Is there any way to remove the path string so that any computer can use the file?

Any help to fix this problem would be greatly appreciated. The macro assigned to the button follows:

Sub load_csv() Dim fStr As String With Application.FileDialog(msoFileDialogFilePicker) .Show If .SelectedItems.Count = 0 Then MsgBox "Cancel Selected" End End If 'fStr is the file path and name of the file you selected. fStr = .SelectedItems(1) End With Range("A1").Select With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Users\laptop\Desktop\CAPTURE.csv", Destination:=Range("$A$1")) .Name = "CAPTURE" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False MsgBox fStr End With End Sub 

Answer 1

Is that what you are trying?

 Sub load_csv() Dim fStr As String With Application.FileDialog(msoFileDialogFilePicker) .Show If .SelectedItems.Count = 0 Then MsgBox "Cancel Selected" Exit Sub End If 'fStr is the file path and name of the file you selected. fStr = .SelectedItems(1) End With With ThisWorkbook.Sheets("Data").QueryTables.Add(Connection:= _ "TEXT;" & fStr, Destination:=Range("$A$1")) .Name = "CAPTURE" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub 

Answer 2

For Excel on Mac, it seems that the QueryTable object does not support the "PreserveFormatting" and "RefreshPeriod" properties and will give you a run-time error if you try to set them.

In addition, Application.FileDialog also does not work with Mac, but this is described in other posts.

For Mac:

 Sub load_csv() Dim fStr As String fStr = "Macintosh HD:Users:anthony:Documents:example.csv" 'Keeping file String simple for example. With ThisWorkbook.Sheets("Data").QueryTables.Add(Connection:= _ "TEXT;" & fStr, Destination:=Range("$A$1")) .Name = "CAPTURE" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False '.PreserveFormatting = True **commented out for Mac .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True '.RefreshPeriod = 0 **commented out for Mac .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub