Results 1 to 5 of 5

Error 91 object variable block variable not set when importing a CVS file

Threaded View

  1. #1
    Registered User
    Join Date
    04-18-2011
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Error 91 object variable block variable not set when importing a CVS file

    Hi there.

    I have a Macro to import a CVs file into a specific spreadsheet. It works but I keep trapping an error 91 object variable block variable not set. I know I have to issue a Set command but dont know exactly what object I am setting. Any help clarifying this would be appreciated.

    I also notice that whenever i run the macro. the QueryTablecount increments.

    Sub ImportStatValues()
    Dim ConnFilename As String ' Need to define the string to point to the import source
    Dim InputFilename As String ' Need to define the string to point to the import source
    
    On Error GoTo ErrHandler
    
    Sheets("ImportedStats").Activate
    Range("A4").Select
    
    ConnFilename = "TEXT;" & Worksheets("Master").Range("E3") & "\" & Worksheets("Master").Range("E4")
    
    InputFilename = Worksheets("Master").Range("E4")
    
    With Sheets("ImportedStats").QueryTables.Add(Connection:=ConnFilename _
     , Destination:=Sheets("ImportedStats").Range("A4"))
     .Name = InputFilename
     .FieldNames = True
     .RowNumbers = False
     .FillAdjacentFormulas = False
     .PreserveFormatting = True
     .RefreshOnFileOpen = False
     .RefreshStyle = xlInsertDeleteCells
     .SavePassword = False
     .SaveData = True
     .AdjustColumnWidth = False ' Preserve the formating for the Imported Stats sheet
     .RefreshPeriod = 0
     .TextFilePromptOnRefresh = False
     .TextFilePlatform = 850
     .TextFileStartRow = 1
     .TextFileParseType = xlDelimited
     .TextFileTextQualifier = xlTextQualifierDoubleQuote
     .TextFileConsecutiveDelimiter = False
     .TextFileTabDelimiter = False
     .TextFileSemicolonDelimiter = False
     .TextFileCommaDelimiter = True
     .TextFileSpaceDelimiter = False
     .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
     .TextFileTrailingMinusNumbers = True
     .Refresh BackgroundQuery:=False
    End With
    
    MsgBox ("Import was completed successfully")
    
    ErrHandler:
      If Err Then
        MsgBox "Unexpected error occurred. " & Error(Err) & " - " & Err
      End If
    End Sub
    Last edited by ReportBuilder2011; 05-13-2011 at 11:47 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1