VB Code for MS Query
 

Hi Everyone,
I have a piece of code in Excel 2000 to retrieve some
data from Access 2000 using MS Query and it works fine.
The problem is that I need to copy down the formulas to
the right of the data and it does not do it even tough I
have the line: FillAdjacentFormulas=True on it.
The funny thing is that if I do a straight "refresh" in
the worksheet it copies all the formulas perfectly. The
reason why I need to do it using a macro is because I
need the user to select some criteria from another
worksheet. (I tested this and it works too)

Here's the code:

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y(
_
"ODBC;DSN=MS Access Database;DBQ=S:\FF
Files\Profit Model.mdb;DefaultDir=S:\FF
Files;DriverId=25;FIL=MS Access;MaxBufferSize=" _
), Array("2048;PageTimeout=5;")),
Destination:=Range("A10"))
.CommandText = Array( _
----SQL Statement Here----
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Is there anything that I am missing or doing wrong?
I thank you all in advance
Gustavo

VB Code for MS Query
 

Gustavo

It will not fill the adjacent formulas when you first create the querytable.
I'm not sure why. If you add another Refresh line, it should work.

.Refresh BackgroundQuery:=False
.Refresh BackgroundQuery:=False
End With

I there a particular reason you're creating a new querytable every time? If
you can use the same querytable but just need different criteria, you might
consider using parameters.

http://www.dicks-clicks.com/excel/Ex...htm#Parameters

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Gustavo" wrote in message
...
Hi Everyone,
I have a piece of code in Excel 2000 to retrieve some
data from Access 2000 using MS Query and it works fine.
The problem is that I need to copy down the formulas to
the right of the data and it does not do it even tough I
have the line: FillAdjacentFormulas=True on it.
The funny thing is that if I do a straight "refresh" in
the worksheet it copies all the formulas perfectly. The
reason why I need to do it using a macro is because I
need the user to select some criteria from another
worksheet. (I tested this and it works too)

Here's the code:

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y(
_
"ODBC;DSN=MS Access Database;DBQ=S:\FF
Files\Profit Model.mdb;DefaultDir=S:\FF
Files;DriverId=25;FIL=MS Access;MaxBufferSize=" _
), Array("2048;PageTimeout=5;")),
Destination:=Range("A10"))
.CommandText = Array( _
----SQL Statement Here----
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Is there anything that I am missing or doing wrong?
I thank you all in advance
Gustavo

VB Code for MS Query
 

Thanks a lot Dick!
I noticed that the extra refresh line works.It just takes
twice as long to complete the task. Strange thing this
one.
I can't have the user entering a customer number
everytime because we have over 25,000 and they just won't
remember. I had a filtered list of them in another
worksheet that they use to select and from there I am
taking the criteria. It works just fine. I looked at the
code in the website you recommended and I might use that
method now.
Thanks a lot for your help and response
Gustavo


-----Original Message-----
Gustavo

It will not fill the adjacent formulas when you first
create the querytable.
I'm not sure why. If you add another Refresh line, it
should work.

.Refresh BackgroundQuery:=False
.Refresh BackgroundQuery:=False
End With

I there a particular reason you're creating a new
querytable every time? If
you can use the same querytable but just need different
criteria, you might
consider using parameters.

http://www.dicks-
clicks.com/excel/ExternalData6.htm#Parameters

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Gustavo" wrote in
message
...
Hi Everyone,
I have a piece of code in Excel 2000 to retrieve some
data from Access 2000 using MS Query and it works fine.
The problem is that I need to copy down the formulas to
the right of the data and it does not do it even tough
I
have the line: FillAdjacentFormulas=True on it.
The funny thing is that if I do a straight "refresh" in
the worksheet it copies all the formulas perfectly. The
reason why I need to do it using a macro is because I
need the user to select some criteria from another
worksheet. (I tested this and it works too)

Here's the code:

With ActiveSheet.QueryTables.Add(Connection:=Array
(Array(
_
"ODBC;DSN=MS Access Database;DBQ=S:\FF
Files\Profit Model.mdb;DefaultDir=S:\FF
Files;DriverId=25;FIL=MS Access;MaxBufferSize=" _
), Array("2048;PageTimeout=5;")),
Destination:=Range("A10"))
.CommandText = Array( _
----SQL Statement Here----
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Is there anything that I am missing or doing wrong?
I thank you all in advance
Gustavo


.

VB Code for MS Query
 

Gustavo

If you are building the SQL statement in your code, then I still think you
shouldn't need to create the querytable each time, even without parameters.
You can use code like this

Dim sSql As String

sSql = "build your string here"

Sheet1.QueryTables(1).CommandText = sSql
Sheet1.QueryTables(1).Refresh

I'll bet that would take care of the adjacent formula problem too.

Good luck.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Gustavo" wrote in message
...
Thanks a lot Dick!
I noticed that the extra refresh line works.It just takes
twice as long to complete the task. Strange thing this
one.
I can't have the user entering a customer number
everytime because we have over 25,000 and they just won't
remember. I had a filtered list of them in another
worksheet that they use to select and from there I am
taking the criteria. It works just fine. I looked at the
code in the website you recommended and I might use that
method now.
Thanks a lot for your help and response
Gustavo


-----Original Message-----
Gustavo

It will not fill the adjacent formulas when you first
create the querytable.
I'm not sure why. If you add another Refresh line, it
should work.

.Refresh BackgroundQuery:=False
.Refresh BackgroundQuery:=False
End With

I there a particular reason you're creating a new
querytable every time? If
you can use the same querytable but just need different
criteria, you might
consider using parameters.

http://www.dicks-
clicks.com/excel/ExternalData6.htm#Parameters

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Gustavo" wrote in
message
...
Hi Everyone,
I have a piece of code in Excel 2000 to retrieve some
data from Access 2000 using MS Query and it works fine.
The problem is that I need to copy down the formulas to
the right of the data and it does not do it even tough
I
have the line: FillAdjacentFormulas=True on it.
The funny thing is that if I do a straight "refresh" in
the worksheet it copies all the formulas perfectly. The
reason why I need to do it using a macro is because I
need the user to select some criteria from another
worksheet. (I tested this and it works too)

Here's the code:

With ActiveSheet.QueryTables.Add(Connection:=Array
(Array(
_
"ODBC;DSN=MS Access Database;DBQ=S:\FF
Files\Profit Model.mdb;DefaultDir=S:\FF
Files;DriverId=25;FIL=MS Access;MaxBufferSize=" _
), Array("2048;PageTimeout=5;")),
Destination:=Range("A10"))
.CommandText = Array( _
----SQL Statement Here----
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Is there anything that I am missing or doing wrong?
I thank you all in advance
Gustavo


.