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
Bookmarks