VB Problem - Help!

S

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: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 Su
 
G

Guest

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 said:
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
Click to expand...
 
G

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
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA: Copy from columns on multiple worksheets 0
ACTIVESHEET.PASTE not working. 3
Problem with Remove Subtotal in Macro 1
Still need some help with nested macro. 2
range referencing 1
macro working very slow 1
macro ...if command, I suppose... 4
Macro resets my calculation to manual 11

Top