+ Reply to Thread
Results 1 to 5 of 5

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

Hybrid 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.

  2. #2
    Forum Contributor
    Join Date
    04-11-2011
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    325

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

    Add code tags around your code please.

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

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

    Can i ask what are code tags

  4. #4
    Forum Contributor
    Join Date
    04-11-2011
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    325

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

    Before your code type brackets. [ ]
    In between the first brackets type the word code.

    After the code end with brackets and the word /code.



    Also if you go advanced there is a button that automatically makes the tags for you so you can paste the code in between them. This will help members read your code easier.

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

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

    Much appreciated. hopefully someone can help me with this now that its somewhat easier to read

+ Reply to Thread

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