Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |