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

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

Yet more VBA Help!
 

First try replacing:

With ActiveSheet.QueryTables.Add(Connection:= _

with:

With Sheets("ACL").QueryTables.Add(Connection:= _


--
Gary''s Student - gsnu200730

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

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

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