Debug Error
 

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

Debug Error
 

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

--
Cheers
macropod
[MVP - Microsoft Word]


"Saxman" wrote in message ...
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

Debug Error
 

macropod wrote:

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.

Debug Error
 

macropod wrote:

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.