Yet more VBA Help!

[Chris]

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

 

[Roger Govier]

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

 

[Gary''s Student]

First try replacing:

With ActiveSheet.QueryTables.Add(Connection:= _

with:

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

 

[steve_doc]

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]

When i do this and any of the other suggestions, a runtime error appears?
Any other ideas on this?

 

[steve_doc]

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