Help with outputting spreadsheet with multiple worksheets

[Dirt Bike]

So I did some research and came across a similar question and the helpful guy
linked to his accessmvp site or some such. That helped me get somewhat off
ground zero. But, and please don't laugh because i am such a VBA idiot
but.....when I try to put a WHERE in the code below I either get a Error,
expecting at least one parameter or "the database cannot find table or query
'False'..."

When I pull out the WHERE I get the results I need. The SQL look at a query
to do the same thing is WHERE (((Forecast.WhoResp)="Dudley"));

How do i do that in VBA with all the requisite & "].[" and the like...geese
this is killing me to be soooooooo ignorant.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Set objDB = CurrentDb

Const strFileName As String = "test"
Const strQName As String = "zExportQuery"

Dim strWorksheet(3) As String
strWorksheet(0) = "Dudley"
strWorksheet(1) = "Worksheet2"
strExcelFile = "W:\datafiles\spreadsheet"


objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & "].[" _
& strWorksheet(0) & "] FROM qryForecast WHERE " & _
"[" & qryForecast & "].[" & WhoResp & "]" = "Dudley" & ";"

objDB.Close
MsgBox "Succesful!"
Set objDB = Nothing


Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

 

[Ken Snell \(MVP\)]

Sounds like the approach shown in this article is what you're seeking:

Create a Query and Export multiple "filtered" versions of a Query (based on
data in another table) to separate Worksheets within one EXCEL file via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#FilterExportSameFile

 

[Dirt Bike]

You are correct!!!! That is exactly where I got my start thanks to you Ken.

My imediate problem is much, much, much more fundimental.

My problem is my inability to convert from SQL to VBA the following:

The SELECT and the FROM are working great the WHERE is my sticking point.

SELECT Forecast.FinishedGood, Forecast.Qty, Forecast.WhoResp, Forecast.AutoID
FROM Forecast
WHERE (((Forecast.WhoResp)="Dudley"));

 

[pietlinden]

You are correct!!!!  That is exactly where I got my start thanks to youKen.  

My imediate problem is much, much, much more fundimental.

My problem is my inability to convert from SQL to VBA the following:

The SELECT and the FROM are working great the WHERE is my sticking point.

SELECT Forecast.FinishedGood, Forecast.Qty, Forecast.WhoResp, Forecast.AutoID
FROM Forecast
WHERE (((Forecast.WhoResp)="Dudley"));

strSQL ="SELECT Forecast.FinishedGood, Forecast.Qty, Forecast.WhoResp,
Forecast.AutoID FROM Forecast WHERE (((Forecast.WhoResp)= ' Dudley
' ));"

Instead of using double quotes, use single quotes. (spaces added for
clarity).

 

[Ken Snell \(MVP\)]

Can you post the code that you've created from the example? Let's see
exactly what you're doing; that'll help us help you.