VB Problem - Help!

[SamuelT]

Hi all,

I'm trying to run the below macro that imports a text (.txt) file int
a spreadsheet. However, when I run the macro I get a Runtime erro
message. The debugger points to the line highlighted red below.

Can anyone suggest what's wrong here?

TIA,

SamuelT


Sub Import_Control_Sheet()
'
' Import_Control_Sheet Macro
' Macro recorded 06/09/2005 by PalletV
'

'
Range("A8").Select
ActiveCell.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;U:\Card_Processing\PAD\Reconcilliation\Pending", _
Destination:=Range("A8"))
.Name = "From_Notes_8"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 2, 1, 1, 2, 2, 2, 2, 2, 1
2, 2)
.Refresh BackgroundQuery:=False
End With
Columns("G:G").Select
Selection.NumberFormat = "0.00"
Columns("P").Select
Selection.NumberFormat = "0.00"
Range("P8").Select
ActiveCell.FormulaR1C1 = "=RC[-6]/100"
Range("P8").Select
Selection.Copy
ActiveCell.Offset(0, -6).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 6).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -6).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 8).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=IF(RC[-17]=14,1,"" "")"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveCell.Offset(-7, -8).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.Offset(2, 0).Range("A1").Select
Columns("P").Select
Selection.EntireColumn.Hidden = True
Columns("P").Select
Selection.ClearContents
Columns("C:L").EntireColumn.Select
Selection.Columns.AutoFit
ActiveCell.Offset(0, -2).Range("A1").Select
ChDir "V:\Card_Processing\PAD\Reconcilliation\Done"
ActiveWorkbook.SaveAs Filename:= _

"V:\Card_Processing\PAD\Reconcilliation\Done\Control_Template.xls"
FileFormat _
:=xlNormal, Password:="", WriteResPassword:=""
ReadOnlyRecommended:= _
False, CreateBackup:=False
End Su

 

[Tom Ogilvy]

It may be highlighted in red in the Excel Forum, but in other forums where
this would have a better chance of being answered, there is no highlighting.
If you want help, indicate in some other way what line is problematic.

--
Regards,
Tom Ogilvy

Hi all,

I'm trying to run the below macro that imports a text (.txt) file into
a spreadsheet. However, when I run the macro I get a Runtime error
message. The debugger points to the line highlighted red below.

Can anyone suggest what's wrong here?

TIA,

SamuelT


Sub Import_Control_Sheet()
'
' Import_Control_Sheet Macro
' Macro recorded 06/09/2005 by PalletV
'

'
Range("A8").Select
ActiveCell.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;U:\Card_Processing\PAD\Reconcilliation\Pending", _
Destination:=Range("A8"))
.Name = "From_Notes_8"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 2, 1, 1, 2, 2, 2, 2, 2, 1,
2, 2)
.Refresh BackgroundQuery:=False
End With
Columns("G:G").Select
Selection.NumberFormat = "0.00"
Columns("P").Select
Selection.NumberFormat = "0.00"
Range("P8").Select
ActiveCell.FormulaR1C1 = "=RC[-6]/100"
Range("P8").Select
Selection.Copy
ActiveCell.Offset(0, -6).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 6).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -6).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 8).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=IF(RC[-17]=14,1,"" "")"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveCell.Offset(-7, -8).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.Offset(2, 0).Range("A1").Select
Columns("P").Select
Selection.EntireColumn.Hidden = True
Columns("P").Select
Selection.ClearContents
Columns("C:L").EntireColumn.Select
Selection.Columns.AutoFit
ActiveCell.Offset(0, -2).Range("A1").Select
ChDir "V:\Card_Processing\PAD\Reconcilliation\Done"
ActiveWorkbook.SaveAs Filename:= _

"V:\Card_Processing\PAD\Reconcilliation\Done\Control_Template.xls",
FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
End Sub

 

[gti_jobert]

if the line .Refresh BackgroundQuery:=False is returning an error the
my answer to your problem would be to remove it from the macro :cool

 

[Tom Ogilvy]

You realize that if you do that, then the macro does nothing. Refreshing the
query is the same as saying execute the query. So if you don't execute the
query, naturally you will get no errors.