can't get adjacent formulas to fill on a 'querytables' object

[tulipsfree]

Here's the code:

**********************************************************
Sub Macro1()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Prophet\data1\S\S03Q.TXT", Destination:=Range("B4&quot)
.Name = "S03Q_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.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 = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
Range("F19&quot.Select
Application.WindowState = xlMaximized
ActiveWindow.SmallScroll Down:=45
End Sub
**********************************************************

but it won't fill my adjacent formulas?

Thanks

Tulip

 

[xlbo]

Which side of the dataset are the "adjacent formulae" ? - it only works on formulae to the right of the querytable dataset. Only other thing I can think of is that you have a hidden column between the formulae and the dataset

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[tulipsfree]

The right side -- no hidden columns.
Let me as you though -- what exactly does it mean by 'fill'? Does it mean that it will actually 'fill' the adjacent cells with the formula from the row above it (as the text data is refreshed incrementally by row)? --or does it just tie into what needs to be an already placed formula?

Also, I can't get any traction when turning off the following property:
'TextFilePromptOnRefresh = False'
{I pulled open the VB online help file, which mentioned something about class files. And in the example given, they first 'Set' something as an object, but I can't figure out how to rebuild this in my example above}

Thanks xlbo,
Tulip

 

[xlbo]

Not sure then tulip - I've never had any problems with it. The filladjacentformulae property basically senses if there is a formula next to the data set and if the dataset changes size, it expands the formula down (or up) to compensate. As to class modules - I managed to get one to work to create an afterrefresh event for a querytable but the xlhelp was wrong - not just misleading but wrong. For better information on class modules, try out the MSDN knowledge base

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[tulipsfree]

Thank you for your time.

Sounds like it does what I had hoped -- if I can just get it to work.
Could it have something to do with the procedure being a macro? --It's as if changes to property settings have no bearing on this procedure...

.RefreshOnFileOpen = True
.TextFilePromptOnRefresh = False
.FillAdjacentFormulas = True

...none of these have had any effect on the (default) text import procedure/macro.

Should (and how would) I take it out of a macro procedure? Do I just change the name of the procedure from "Macro1" to (whatever)?

Thanks,
Tulip

 

[xlbo]

The problem may lie in the fact that you are creating a new querytable each time. I might work if you set up the querytable once and then use
with activesheet.querytables(1)
.filladjacentformulas = true
.refresh
end with

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[tulipsfree]

(#$%&#$%&@$#^) --> This VB stuff is harder than it looks.

I've got two 800+ pg. manuals on VBE and I can't seem to get a simple text import macro to work the way I need it to.

Here's another approach (different manual):

************************
Sub ImportNewText()
Dim qt As QueryTable, ws As Worksheet
Dim fname$
fname = ThisWorkbook.Path + "\S03Q.TXT"
Set ws = Worksheets("QueryTable&quot
ws.Activate
Set qt = ws.QueryTables.Add(Connection:= _
"TEXT;C:\Prophet\data1\S\S03Q.TXT", Destination:=Range("B4&quot)
With qt
.Name = "S03Q.TXT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub
************************

When I run the code, VB gives me a "Script out of range (Error 9)" message -- and then highlights the following line:
Set ws = Worksheets("QueryTable&quot

?????????? -- this thing is driving me nuts -- !!!!!!!!!!!

Thanks in advance for your help...
Tulip

 

[xlbo]

That error generally means you are trying to reference something that isn't there - do you have a sheet called "Querytable" - is there a space ??
eg Query Table
????

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[tulipsfree]

Good Call Geoff !!!!

It's always the simple things!

I'll keep this in mind going forward.

Many Many thanks again Geoff (I'm going to have to start contributing $ soon!)

Tulip