VB Problem - Help!
 

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\Pendi ng", _
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\Contr ol_Template.xls"
FileFormat _
:=xlNormal, Password:="", WriteResPassword:=""
ReadOnlyRecommended:= _
False, CreateBackup:=False
End Su

--
Samuel
-----------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...fo&userid=2750
View this thread: http://www.excelforum.com/showthread.php?threadid=52302

VB Problem - Help!
 

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\Pendi ng", _
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\Contr ol_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

VB Problem - Help!
 

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

--
gti_jober
-----------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063
View this thread: http://www.excelforum.com/showthread.php?threadid=52302

VB Problem - Help!
 

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 :cool:


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