Query VB code Madness-Someone knows how to do this..Really...

[chris23892 via AccessMonster.com]

hello all. been awhile since I've been here.

I have the following VB code behind a command button in acess:

Dim sXL As String, oXL As Object
sXL = "some path with a file name .XLS" 'Full path to your spreadsheet
' Insert the actual name of your query between the quotes below
DoCmd.TransferSpreadsheet acExport, , "Qery name", sXL
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
oXL.WorkBooks.Open FileName:=sXL

works GREAT in acess, I use this snippet all the time.

My question is how do I get this snippet to work in MS Excel? There can't be
that much differnce (or yes there could be, I guess).

I can't imagine it would be that hard to tell MS Excell to do the above code
snippet.

this would be a very handy trick in my toolbox to be able to create a sheet
with code behind command buttons to run all the Acess queries I have.

Any suggestions??
Thanks, looking forward to figuring this one out.

 

[Douglas J. Steele]

You can't.

DoCmd is an object that's part of the Access application. Excel doesn't have
such an object in it.

If you're already in Excel, then use Excel methods to get the data:

With ActiveSheet.QueryTables.Add(Connection:= _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\SomeFolder\SomeFile.mdb;" _
, Destination:=Range("A1"))
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM SomeTable")
.FieldNames = True
.PreserveFormatting = True
.RefreshStyle = xlInsertDeleteCells
.AdjustColumnWidth = True
.PreserveColumnInfo = True
.SourceDataFile = "C:\SomeFolder\SomeFile.mdb"
.Refresh BackgroundQuery:=False
End With

 

[chris23892 via AccessMonster.com]

wow...I see...this is a whole different monster...Let me play with the code
and see if I can't get this wrapped around my mind..

Thanks for the input. Let you know how this turns out..

You can't.

DoCmd is an object that's part of the Access application. Excel doesn't have
such an object in it.

If you're already in Excel, then use Excel methods to get the data:

With ActiveSheet.QueryTables.Add(Connection:= _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\SomeFolder\SomeFile.mdb;" _
, Destination:=Range("A1"))
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM SomeTable")
.FieldNames = True
.PreserveFormatting = True
.RefreshStyle = xlInsertDeleteCells
.AdjustColumnWidth = True
.PreserveColumnInfo = True
.SourceDataFile = "C:\SomeFolder\SomeFile.mdb"
.Refresh BackgroundQuery:=False
End With

hello all. been awhile since I've been here.

[quoted text clipped - 24 lines]

Any suggestions??
Thanks, looking forward to figuring this one out.

 

[chris23892 via AccessMonster.com]

Hummmm....plugged in the data dath, query name and ran the code from my
button in excel and got the following error:

" application-difined or object-defined error"

I don't have a user name or password set up for the DB, so I'm assuming when
excel prompts me, I just click ok.

Question: Noticed in the code it specifes ~some table~

Do I need to make a table?

I really just want to run a query in this acess DB....

You can't.

DoCmd is an object that's part of the Access application. Excel doesn't have
such an object in it.

If you're already in Excel, then use Excel methods to get the data:

With ActiveSheet.QueryTables.Add(Connection:= _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\SomeFolder\SomeFile.mdb;" _
, Destination:=Range("A1"))
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM SomeTable")
.FieldNames = True
.PreserveFormatting = True
.RefreshStyle = xlInsertDeleteCells
.AdjustColumnWidth = True
.PreserveColumnInfo = True
.SourceDataFile = "C:\SomeFolder\SomeFile.mdb"
.Refresh BackgroundQuery:=False
End With

hello all. been awhile since I've been here.

[quoted text clipped - 24 lines]

Any suggestions??
Thanks, looking forward to figuring this one out.

 

[Douglas J. Steele]

Either replace

.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM SomeTable")

with

.CommandType = xlCmdTable
.CommandText = Array("NameOfQuery")

or use

.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM NameOfQuery")



--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Hummmm....plugged in the data dath, query name and ran the code from my
button in excel and got the following error:

" application-difined or object-defined error"

I don't have a user name or password set up for the DB, so I'm assuming
when
excel prompts me, I just click ok.

Question: Noticed in the code it specifes ~some table~

Do I need to make a table?

I really just want to run a query in this acess DB....

You can't.

DoCmd is an object that's part of the Access application. Excel doesn't
have
such an object in it.

If you're already in Excel, then use Excel methods to get the data:

With ActiveSheet.QueryTables.Add(Connection:= _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\SomeFolder\SomeFile.mdb;" _
, Destination:=Range("A1"))
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM SomeTable")
.FieldNames = True
.PreserveFormatting = True
.RefreshStyle = xlInsertDeleteCells
.AdjustColumnWidth = True
.PreserveColumnInfo = True
.SourceDataFile = "C:\SomeFolder\SomeFile.mdb"
.Refresh BackgroundQuery:=False
End With

hello all. been awhile since I've been here.

[quoted text clipped - 24 lines]

Any suggestions??
Thanks, looking forward to figuring this one out.

 

[chris23892 via AccessMonster.com]

well, that solved the runtime error.


Interesting results though...

The output of the query opened in the same sheet I created the button in. How
does one just tell the buttom to run the Query it's pointing too?

Just some back ground....

these queries already output a Excel file, all nice, neat, sorted. I just
want to use Excel to create command buttons to run specified queries.

Is this possibable?

Either replace

.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM SomeTable")

with

.CommandType = xlCmdTable
.CommandText = Array("NameOfQuery")

or use

.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM NameOfQuery")

Hummmm....plugged in the data dath, query name and ran the code from my
button in excel and got the following error:

[quoted text clipped - 39 lines]

 

[Douglas J. Steele]

Don't know what you mean by "just tell the button to run the Query it's
pointing to."

Let's back up a moment. You're dealing with simple Select queries, not
Action queries, correct? (Otherwise using TransferSpreadsheet makes no
sense).

You cannot just run Select queries: Select queries only make sense in terms
of generating a recordset of results and doing something with that recordset
(even if it's only looking at it on the screen).

That being said, is your question about how to get the results to be written
to a specific location on a specific worksheet in the workbook?

Take a look at the first lines of what I'd posted

With ActiveSheet.QueryTables.Add(Connection:= _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\SomeFolder\SomeFile.mdb;" _
, Destination:=Range("A1"))

The data's being written to the sheet where the button exists because of the
use of "ActiveSheet". To have it write somewhere else, replace ActiveSheet
with Sheets("NameOfSheet")

The data's being written starting in column 1 on row 1 because of the
"Destination=Range("A1")". To have it write to some other location on the
worksheet, change the A1.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

well, that solved the runtime error.


Interesting results though...

The output of the query opened in the same sheet I created the button in.
How
does one just tell the buttom to run the Query it's pointing too?

Just some back ground....

these queries already output a Excel file, all nice, neat, sorted. I just
want to use Excel to create command buttons to run specified queries.

Is this possibable?

Either replace

.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM SomeTable")

with

.CommandType = xlCmdTable
.CommandText = Array("NameOfQuery")

or use

.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM NameOfQuery")

Hummmm....plugged in the data dath, query name and ran the code from my
button in excel and got the following error:

[quoted text clipped - 39 lines]

Any suggestions??
Thanks, looking forward to figuring this one out.