VB Problem - Help!

[SamuelT]

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: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:P").Select
Selection.EntireColumn.Hidden = True
Columns("P: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

[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


"SamuelT" wrote:

>
> 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: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:P").Select
> Selection.EntireColumn.Hidden = True
> Columns("P: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
>
>
> --
> SamuelT
> ------------------------------------------------------------------------
> SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501
> View this thread: http://www.excelforum.com/showthread...hreadid=523028
>
>

[gti_jobert]

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

[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.

--
Regards,
Tom Ogilvy


"gti_jobert" wrote:

>
> if the line .Refresh BackgroundQuery:=False is returning an error then
> my answer to your problem would be to remove it from the macro
>
>
> --
> gti_jobert
> ------------------------------------------------------------------------
> gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
> View this thread: http://www.excelforum.com/showthread...hreadid=523028
>
>