Quick Code Conversion

**j.lancaster1** · 08-27-2012, 03:20 PM

Hello,

I just have a simple issue. The following code allows me to select a .csv file and add the file to my current excel file. I want to do the same thing, but with .xls files instead of .csv I know to change the .csv to .xls in the code, but the data is importing in the incorrect format. I know the issue is somewhere in the "Import Text Data" section at the bottom, but I'm not sure which parameter to change to get it to read the .xls files

Private Sub CommandButton1_Click()
Dim strFile As String, strPath As String

    ' Prompt user to select a file
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = ThisWorkbook.Path & "\*.csv"  ' Default path and file type .csv
        '.FilterIndex = 6                                ' File type .csv
        .Title = "Please Select a File"
        .ButtonName = "Open"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count = 0 Then Exit Sub            ' User clicked cancel: Exit
        strFile = .SelectedItems(1)
    End With
           
    If LCase(Right(strFile, 3)) = "csv" Then
    
        strPath = Left(strFile, InStrRev(strFile, "\"))
        strFile = Mid(strFile, Len(strPath) + 1)
        
        With Worksheets.Add(After:=Sheets(Sheets.Count))
            On Error Resume Next
                .Name = Replace(strFile, ".csv", "") 'Name sheet
            On Error GoTo 0
            
            'Import text data
            With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, Destination:=.Range("A1"))
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = True      'Comma delimited
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = Array(1)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
            End With
        End With
        

    Else
    MsgBox "Only select a .csv file type. "
    End If
   
End Sub

**BuckoAk** · 08-28-2012, 01:55 AM

Take a look at this Link
http://www.excelforum.com/excel-prog...using-vba.html

You will need to set the Path and Range per your needs

MyPath = "C:\"
Set sourceRange = mybook.Worksheets(1).Range("A1:C1")

**patel45** · 08-28-2012, 02:04 AM

Sub b()
Dim strFile As String, strPath As String

    ' Prompt user to select a file
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = ThisWorkbook.Path & "\*.xls"
        .Title = "Please Select a File"
        .ButtonName = "Open"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count = 0 Then Exit Sub            ' User clicked cancel: Exit
        strFile = .SelectedItems(1)
    End With
    If LCase(Right(strFile, 3)) = "xls" Then
        strPath = Left(strFile, InStrRev(strFile, "\"))
        strFile = Mid(strFile, Len(strPath) + 1)
        
        With Worksheets.Add(After:=Sheets(Sheets.Count))
            On Error Resume Next
                .Name = Replace(strFile, ".xls", "") 'Name sheet
            On Error GoTo 0
               Workbooks.Open Filename:=strFile
        End With
    Else
    MsgBox "Only select a .xls file type. "
    End If
   
End Sub

**j.lancaster1** · 08-28-2012, 10:33 AM

Thanks guys. I forgot to mention that I needed the new file to import onto a new tab/sheet. Patel45, with your code the new file opens up as it should, but on the tab that the macro was on (and deletes the button). Can you tweak it to where the data is put on a new tab/sheet?