none
Does anyone know why it insists that the ".Title = "choose file"" part is wrong? Millions of thanks! RRS feed

  • Question

  • Private Sub CommandButton1_Click()

    Dim sfilepath As FileDialog
                     
          
        With sfilepath
        
        .Title = "choose file"
        .AllowMultiSelect = False

    End With
        
        If sfilepath.Show <> -1 Then
         

    Exit Sub


    Else


    Dim SH As Worksheet, filename As Variant
      
        
        Set SH = Sheets.Add(after:=Worksheets(Worksheets.Count))
        filename = Application.GetOpenFilename("Text Files (*.csv), *.csv")
                 
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & filename, Destination:=Range("$A$1"))
            .Name = Application.GetOpenFilename
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False

            
    End With
        
       
    End If
        
    End Sub
    Thursday, October 3, 2013 9:50 AM

Answers

  • You need to create the object, not just declare it:

        Dim sfilepath As FileDialog

        'Create the FileDialog object
        Set sfilepath = Application.FileDialog(msoFileDialogFilePicker)
        With sfilepath
            .Title = "choose file"
            .AllowMultiSelect = False
        End With


    Thursday, October 3, 2013 1:11 PM

All replies

  • You need to create the object, not just declare it:

        Dim sfilepath As FileDialog

        'Create the FileDialog object
        Set sfilepath = Application.FileDialog(msoFileDialogFilePicker)
        With sfilepath
            .Title = "choose file"
            .AllowMultiSelect = False
        End With


    Thursday, October 3, 2013 1:11 PM
  • Thanks alot! Problem solved:)
    Thursday, October 3, 2013 7:40 PM
  • Hi

    Thank you for your answer. The problem is fixed now but when I click on the button to import exterior data, the window jumps out three times before I can select the file... Do you know where went wrong ? Thank you !

    Private Sub CommandButton1_Click()

    Dim sfilepath As FileDialog
    Set sfilepath = Application.FileDialog(msoFileDialogFilePicker)
          
        With sfilepath
        
        .Title = "choose file"
        .AllowMultiSelect = False

    End With
        
        If sfilepath.Show = -1 Then
         
    Dim SH As Worksheet, filename As Variant, FN As String

      
        
        Set SH = Sheets.Add(after:=Worksheets(Worksheets.Count))
        filename = Application.GetOpenFilename("Text Files (*.csv), *.csv")

        
        

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & filename, Destination:=Range("$A$1"))
            .Name = Application.GetOpenFilename
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False

            
    End With
        
    Else: Exit Sub


    End If
        
    End Sub

    Sunday, October 6, 2013 9:35 PM
  • You aren't doing anything with the dialog, so don't use it:

    Private Sub CommandButton1_Click()  
    Dim SH As Worksheet
    Dim FileName As Variant

    Set SH = Sheets.Add(after:=Worksheets(Worksheets.Count))
    FileName = Application.GetOpenFilename("Text Files (*.csv), *.csv")
    If TypeName(FileName) = "Boolean" Then Exit Sub

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FileName, Destination:=Range("$A$1"))
            .Name = Application.GetOpenFilename
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False

            
    End With
            
    End Sub

    Monday, October 7, 2013 2:15 PM