MS Access to Excel Code Review

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello -

Could you help me with the code below that I found in an older posting and
was trying to adapt it to my needs? I believe what the code is just missing
is the beginning sub () and the end sub, and maybe the Getcn definition. I am
not familiar with VB in Access and connecting Access to Excel . What I would
like to do is just copy the code and paste it in a new Excel module and just
have it functional. Thank you.

Dim qdf As DAO.Querydef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT [Lawson dept] FROM [OP Volume];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
'
' deleted the DLookup step
'
strMgr = rstMgr![Lawson dept].Value
'
' added ' characters to delimit the manager's last name string
'
strSQL = "SELECT * FROM [OP Volume] WHERE " & "[Lawson dept] = '" & strMgr &
"';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.Sql = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTemp,
"C:\Temp\" & strMgr & Format(Now(), "mmddyyyy") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
 
The code you posted is designed to be run in Access, not Excel. The
CurrentDb method and the DoCmd.TransferDatabase method are methods of the
Access object library and do not exist in Excel. If your aim is to import
data into Excel from a JET ('Access') database from code in an Excel module,
then this code is probably not the best starting point.

I'm sorry that I can't tell you what *would* be the best starting point, as
I'm not an expert in Excel programming. A vague memory is prompting me that
the CopyFromRecordset method of the Excel Range object might be what you
need. If no one else provides a more specific answer here, you might want to
try asking the question in an Excel newsgroup. Meanwhile, here's a link to
the on-line help topic on the CopyFromRecordset method ...

http://office.microsoft.com/client/...t=2&ns=EXCEL.DEV&lcid=2057&pid=CH100890951033
 
Thank you Brendan. You identified the problem. I was pasting the code in
Excel instead of Access (DUM!!). No wonder it wasn't recognizing
"currentdb". Now I pasted it in access and it works better. I still need to
address a few issues, but I want to give it a shot before I ask for help.
That way I can probably formulate my question better. Thank you!

Brendan Reynolds said:
The code you posted is designed to be run in Access, not Excel. The
CurrentDb method and the DoCmd.TransferDatabase method are methods of the
Access object library and do not exist in Excel. If your aim is to import
data into Excel from a JET ('Access') database from code in an Excel module,
then this code is probably not the best starting point.

I'm sorry that I can't tell you what *would* be the best starting point, as
I'm not an expert in Excel programming. A vague memory is prompting me that
the CopyFromRecordset method of the Excel Range object might be what you
need. If no one else provides a more specific answer here, you might want to
try asking the question in an Excel newsgroup. Meanwhile, here's a link to
the on-line help topic on the CopyFromRecordset method ...

http://office.microsoft.com/client/...t=2&ns=EXCEL.DEV&lcid=2057&pid=CH100890951033

--
Brendan Reynolds

Filo said:
Hello -

Could you help me with the code below that I found in an older posting and
was trying to adapt it to my needs? I believe what the code is just
missing
is the beginning sub () and the end sub, and maybe the Getcn definition. I
am
not familiar with VB in Access and connecting Access to Excel . What I
would
like to do is just copy the code and paste it in a new Excel module and
just
have it functional. Thank you.

Dim qdf As DAO.Querydef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT [Lawson dept] FROM [OP Volume];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
'
' deleted the DLookup step
'
strMgr = rstMgr![Lawson dept].Value
'
' added ' characters to delimit the manager's last name string
'
strSQL = "SELECT * FROM [OP Volume] WHERE " & "[Lawson dept] = '" & strMgr
&
"';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.Sql = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTemp,
"C:\Temp\" & strMgr & Format(Now(), "mmddyyyy") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
Click to expand...
Click to expand...
 
Back
Top