Import CSV Files Excel Multiple Select GetOpenFilename won't work argh!!!

[lgjmac]

Please please help me work out what is wrong with the below code. I simply want to select several files and process them into one worksheet in the same way; i recorded the processing, modified it to include and array in Y and put multiselect to true but it simply won't get past the GetOpenFilename part

Private Sub CommandButton1_Click()
       Dim X As Variant

       'Opens the dialog
       X = Application.GetOpenFilename("CSV Files (*.csv),*.csv,Text Files (*.txt),*.txt", 4, "Select Files", , True)
       
       

       'Loops through every file that is selected and opens each one
       For Y = 1 To UBound(X)

With ActiveSheet.QueryTables.Add(Connection:= _
        "X(Y)", Destination _
        :=Range("1,(Y+(Y-1))"))
        .Name = "X(Y)"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlMacintosh
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = True
        .TextFileOtherDelimiter = """"
        .TextFileColumnDataTypes = Array(9, 5, 9, 1, 9, 9)
        .Refresh BackgroundQuery:=False
        .UseListObject = False
    End With

Next Y

End Sub

Private Sub UserForm_Click()

End Sub

[si_the_geek]

Welcome to VBForums

Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

[Siddharth Rout]

Welcome to the Forums

There is nothing wrong with your GetOpenFilename code. The problem is with this line

With ActiveSheet.QueryTables.Add(Connection:= "X(Y)"

'
'~~> Rest of the code
'

The X(Y) is within quotes and hence it become a string and not a "variable"

Try it without the quotes. For example

With ActiveSheet.QueryTables.Add(Connection:= X(Y)

'
'~~> Rest of the code
'

[lgjmac]

I'm also trying to make a macro that will insert two cells between each of my data points (in a column) so that they are in line with other parts of my data which is more complete.... Any ideas why the below won't work?

Thank you for your time.

Sub FinalAttempt()

Dim S As Long
Dim X


X = ActiveCell

Range("X:X.End(xlDown)").Select

S = Selection.Rows.Count

Dim L As Integer

For L = 1 To L = S Step 1

Range("ActiveCell:ActiveCell.End(xlDown)").Select

S = Selection.Rows.Count


Selection.Cut ("ActiveCell.Offset(3,0)")

Cells("X.Offset(L+4,0)").Select

Next L

End Sub

[lgjmac]

Also I now have as you suggested (see below) but still stuck on OpenFileName

Am using Mac Excel VBA recent edition 2011 or perhaps 2012

Private Sub CommandButton1_Click()
Dim X As Variant

'Opens the dialog
X = Application.GetOpenFilename("CSV Files (*.csv),*.csv,Text Files (*.txt),*.txt", 4, "Select Files", , True)



'Loops through every file that is selected and opens each one
For Y = 1 To UBound(X)

With ActiveSheet.QueryTables.Add(Connection:=X(Y), Destination _
:=Range("1,(Y+(Y-1))"))
.Name = "X(Y)"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlMacintosh
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileOtherDelimiter = """"
.TextFileColumnDataTypes = Array(9, 5, 9, 1, 9, 9)
.Refresh BackgroundQuery:=False
.UseListObject = False
End With

Next Y

End Sub