Ho Do I Do Loop Query Export - Help loosing sleep over this one.

[Jonno]

Hi, VBA newbie needs rescuing. I am trying to export the same query with a
changing parameter (manager name) to the same destination and then run a
macro to do some formatting in excel for each sequence.

I have progressed to the following but its not picking up the Manager
reference, please could somebody help! or suggest alternate approach.

Function ExportMgr()

Dim mydb As DAO.Database
Dim myset As DAO.Recordset
Dim Manager as String

Set mydb = CurrentDb
Set myset = mydb.OpenRecordSet("Managers List")

Do Until myset.EOF

Manager = myset![Manager name]

CurrentDb.QueryDefs("ManagerQuery2").SQL = "SELECT* FROM ManagerQuery1 WHERE
Manager name = Manager"

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel7,"ManagerQuery2","C:\Temp\Details.xls"

DoCmd.Run Macro Format

myset.MoveNext

Loop

End Function

 

[Douglas J. Steele]

CurrentDb.QueryDefs("ManagerQuery2").SQL = "SELECT* FROM ManagerQuery1 WHERE
Manager name = """ & Manager & """"

The variable needs to be outside of the SQL string: the Jet Engine doesn't
know anything about VBA variables. And since you're dealing with a text
value, you need all those extra quotes to ensure that quotes are put around
the name. If you knew for certain that you'd never have any names with
apostrophes in them (O'Riley), you could use

CurrentDb.QueryDefs("ManagerQuery2").SQL = "SELECT* FROM ManagerQuery1 WHERE
Manager name = '" & Manager & "'"

but I wouldn't recommend that.

 

[Jonno]

Many Thanks Doug for your assistance, I shall give this a try.

CurrentDb.QueryDefs("ManagerQuery2").SQL = "SELECT* FROM ManagerQuery1 WHERE
Manager name = """ & Manager & """"

The variable needs to be outside of the SQL string: the Jet Engine doesn't
know anything about VBA variables. And since you're dealing with a text
value, you need all those extra quotes to ensure that quotes are put around
the name. If you knew for certain that you'd never have any names with
apostrophes in them (O'Riley), you could use

CurrentDb.QueryDefs("ManagerQuery2").SQL = "SELECT* FROM ManagerQuery1 WHERE
Manager name = '" & Manager & "'"

but I wouldn't recommend that.



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi, VBA newbie needs rescuing. I am trying to export the same query with
a
changing parameter (manager name) to the same destination and then run a
macro to do some formatting in excel for each sequence.

I have progressed to the following but its not picking up the Manager
reference, please could somebody help! or suggest alternate approach.

Function ExportMgr()

Dim mydb As DAO.Database
Dim myset As DAO.Recordset
Dim Manager as String

Set mydb = CurrentDb
Set myset = mydb.OpenRecordSet("Managers List")

Do Until myset.EOF

Manager = myset![Manager name]

CurrentDb.QueryDefs("ManagerQuery2").SQL = "SELECT* FROM ManagerQuery1
WHERE
Manager name = Manager"

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel7,"ManagerQuery2","C:\Temp\Details.xls"

DoCmd.Run Macro Format

myset.MoveNext

Loop

End Function