Debug Error

[Saxman]

Below is a copy of a macro which runs fine with Windows XP, but when I
try to run it with Vista I get a debug error at the bottom of the
Destination:=Range _ which states:

..Refresh BackgroundQuery:=False

I have tried changing it to False, but I still get errors.

Any ideas what the problem might be?


Sub Horsedata()
'
' Horsedata Macro
'
' Keyboard Shortcut: Ctrl+z
'
Sheets("Horsecopy").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\user\Desktop\hdata.txt",
Destination:=Range _
("$A$1"))
.Name = "hdata"
.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 = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("A1:H1501").Select
Selection.Copy
Sheets("Horselist").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Horsecopy").Select
Cells.Select
Application.CutCopyMode = False
Selection.QueryTable.Delete
Selection.ClearContents
Sheets("Horselist").Select
Range("K29:M30").Select
Selection.ClearContents
Range("A1").Select
End Sub

 

[macropod]

Hi Saxman,

With your new OS, are the pathnames exactly the same and do you have permission to access files in the folder 'C:\Documents and
Settings\user\Desktop\'? Is the file 'hdata.txt' in that location?

Also FWIW, your code could be made more efficient by doing away with the selections. For example:
Sub Horsedata()
' Horsedata Macro
' Keyboard Shortcut: Ctrl+z
With Sheets("Horsecopy")
With .QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\user\Desktop\hdata.txt", _
Destination:=Range("$A$1"))
.Name = "hdata"
.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 = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
.Range("A1:H1501").Copy
Sheets("Horselist").Range("B2").Paste
.QueryTable.Delete
.ClearContents
Sheets("Horselist").Range("K29:M30").ClearContents
End With
End Sub

 

[Saxman]

With your new OS, are the pathnames exactly the same and do you have
permission to access files in the folder 'C:\Documents and
Settings\user\Desktop\'? Is the file 'hdata.txt' in that location?

The file is correct, but I think you have solved it with Vista.

It will probably be a revised path, something like...

C:\users\John\Desktop\hdata

rather than...

C:\Desktop\hdata.


I thought file handling was simpler with Vista?

Firefox won't even execute on it this morning!

I'll post later with my findings.

 

[Saxman]

With your new OS, are the pathnames exactly the same and do you have
permission to access files in the folder 'C:\Documents and
Settings\user\Desktop\'? Is the file 'hdata.txt' in that location?

You were absolutely right with your assumption. I don't know why I
didn't think of it myself. I checked the file name, but not the path.

Thank you very much.