Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Yet more VBA Help!
Hi,
By recording a Macro and using the text import wizard i have obtained the following code at the end of this post. However, this always places the data in the active worksheet, and i would like to place in a worksheet called ACL. Can anyone show me how to amend this code to do this? Thanks in advance Chris Here's the code: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 18/06/2007 by chrisr3 ' ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;S:\shared\NCSL\Statistics\Headship\Headship_ Outgoing_Account_Code_Log.txt" _ , Destination:=Range("A1")) .Name = "Headship_Outgoing_Account_Code_Log" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Yet more VBA Help!
Hi Chris
Just add the Sheet name before Range(A1) in the destination "TEXT;S:\shared\NCSL\Statistics\Headship\Headship_ Outgoing_Account_Code_Log.txt" _ , Destination:=Sheets("ACL").Range("A1")) -- Regards Roger Govier "Chris" wrote in message ... Hi, By recording a Macro and using the text import wizard i have obtained the following code at the end of this post. However, this always places the data in the active worksheet, and i would like to place in a worksheet called ACL. Can anyone show me how to amend this code to do this? Thanks in advance Chris Here's the code: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 18/06/2007 by chrisr3 ' ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;S:\shared\NCSL\Statistics\Headship\Headship_ Outgoing_Account_Code_Log.txt" _ , Destination:=Range("A1")) .Name = "Headship_Outgoing_Account_Code_Log" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Yet more VBA Help!
First try replacing:
With ActiveSheet.QueryTables.Add(Connection:= _ with: With Sheets("ACL").QueryTables.Add(Connection:= _ -- Gary''s Student - gsnu200730 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Yet more VBA Help!
Declare a variable and set reference for that worksheet
Dim wsACL as Worksheet 'variable declraration Set wsACL = ActiveWorkbook.WorkSheets("ACL") 'set referencce ' it would be good practice to first check if that worksheet name exists. Then just replace 'ActiveSheet' with wsACL NOTE - may need to adjust some of your code to work with this as I am at work the above is untested HTH Steve "Chris" wrote: Hi, By recording a Macro and using the text import wizard i have obtained the following code at the end of this post. However, this always places the data in the active worksheet, and i would like to place in a worksheet called ACL. Can anyone show me how to amend this code to do this? Thanks in advance Chris Here's the code: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 18/06/2007 by chrisr3 ' ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;S:\shared\NCSL\Statistics\Headship\Headship_ Outgoing_Account_Code_Log.txt" _ , Destination:=Range("A1")) .Name = "Headship_Outgoing_Account_Code_Log" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Yet more VBA Help!
When i do this and any of the other suggestions, a runtime error appears?
Any other ideas on this? "Roger Govier" wrote: Hi Chris Just add the Sheet name before Range(A1) in the destination "TEXT;S:\shared\NCSL\Statistics\Headship\Headship_ Outgoing_Account_Code_Log.txt" _ , Destination:=Sheets("ACL").Range("A1")) -- Regards Roger Govier "Chris" wrote in message ... Hi, By recording a Macro and using the text import wizard i have obtained the following code at the end of this post. However, this always places the data in the active worksheet, and i would like to place in a worksheet called ACL. Can anyone show me how to amend this code to do this? Thanks in advance Chris Here's the code: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 18/06/2007 by chrisr3 ' ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;S:\shared\NCSL\Statistics\Headship\Headship_ Outgoing_Account_Code_Log.txt" _ , Destination:=Range("A1")) .Name = "Headship_Outgoing_Account_Code_Log" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Yet more VBA Help!
Using your recoreded macro and modifying the path to the text file to reflect
my system for testing purpose. Replace With ActiveSheet.QueryTables.Add(your Connection String) with With ActiveWorkbook.Sheets(2).QueryTables.Add(your Connection String) where 2 is the index number of the sheet you want the data on. And replace Destination:=Range("A1")) with Destination:=ActiveWorkbook.Sheets(2).Range("A1")) Tested and this works "Chris" wrote: When i do this and any of the other suggestions, a runtime error appears? Any other ideas on this? "Roger Govier" wrote: Hi Chris Just add the Sheet name before Range(A1) in the destination "TEXT;S:\shared\NCSL\Statistics\Headship\Headship_ Outgoing_Account_Code_Log.txt" _ , Destination:=Sheets("ACL").Range("A1")) -- Regards Roger Govier "Chris" wrote in message ... Hi, By recording a Macro and using the text import wizard i have obtained the following code at the end of this post. However, this always places the data in the active worksheet, and i would like to place in a worksheet called ACL. Can anyone show me how to amend this code to do this? Thanks in advance Chris Here's the code: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 18/06/2007 by chrisr3 ' ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;S:\shared\NCSL\Statistics\Headship\Headship_ Outgoing_Account_Code_Log.txt" _ , Destination:=Range("A1")) .Name = "Headship_Outgoing_Account_Code_Log" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|