Yet more VBA Help!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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"))
 
First try replacing:

With ActiveSheet.QueryTables.Add(Connection:= _

with:

With Sheets("ACL").QueryTables.Add(Connection:= _
 
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
 
When i do this and any of the other suggestions, a runtime error appears?
Any other ideas on this?
 
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
 
Back
Top