Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Debug Error | Excel Discussion (Misc queries) | |||
Debug Error in Code | Excel Discussion (Misc queries) | |||
DeBug | Excel Discussion (Misc queries) | |||
How do I resolve debug error macro Excel 2000? At least, any lead | Excel Discussion (Misc queries) | |||
help with debug | New Users to Excel |