DoCmd.TransferSpreadsheet

[corkster]

I am trying to export to Excel using docmd.transferspreadsheet within
an MS Access 2003 Form. The code below automatically exports by
coordinator to a specific workbook and the employees associated with
that coordinator into multiple sheets for each employee within that
workbook. I know you can start exporting in a specific range of
fields, but with the code I have below I am not quite sure of the
syntax to do this. I would like to specify the starting cell in Excel
for it to export to (IE B1 cell). Please if you need more information
or this isn't the forum for this let me know.

Dim strSQL As String
Dim dbD As DAO.Database
Dim qdfExport As DAO.QueryDef
Dim rscoordinator As DAO.Recordset
Dim strFileSpec As String

Set dbD = CurrentDb()
Set qdfExport = dbD.QueryDefs("qrytemp")
Set rscoordinator = dbD.OpenRecordset("Query2", dbOpenSnapshot)
Set rsemployee = dbD.OpenRecordset("Query2", dbOpenSnapshot)
Do Until rscoordinator.EOF
Do Until rsemployee.EOF


'qdfExport.SQL = "SELECT * FROM employee_coordinator WHERE
[coordinator]=""" & rscoordinator.Fields("coordinator").Value & " "";"
qdfExport.SQL = "SELECT * FROM employee_coordinator INNER JOIN data1 ON
employee_coordinator.emplid = data1.employeeid WHERE [coordinator] =
""" & rscoordinator.Fields("coordinator").Value & " "";"
qdfExport.SQL = "SELECT * FROM employee_coordinator INNER JOIN data1 ON
employee_coordinator.emplid = data1.employeeid WHERE [data1.employee] =
""" & rsemployee.Fields("employee").Value & " "";"

strFileSpec = "C:\1 corey projects\HEC\testing\" _
& rscoordinator.Fields("coordinator").Value & " " & Format(Date,
"mm-dd-yyyy") & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrytemp",
strFileSpec, True, " " & rsemployee.Fields("employee").Value
rscoordinator.MoveNext
rsemployee.MoveNext
Loop
Loop

Thanks

 

[Ken Snell \(MVP\)]

See this article for information about limited capabilities of the Range
argument for DoCmd.TransferSpreadsheet method:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html

You may need to use Automation and write directly into the EXCEL file if the
above options are not useful.

 

[corkster]

See this article for information about limited capabilities of the Range
argument for DoCmd.TransferSpreadsheet method:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html

You may need to use Automation and write directly into the EXCEL file if the
above options are not useful.

--

Ken Snell
<MS ACCESS MVP>

I am trying to export to Excel using docmd.transferspreadsheet within
an MS Access 2003 Form. The code below automatically exports by
coordinator to a specific workbook and the employees associated with
that coordinator into multiple sheets for each employee within that
workbook. I know you can start exporting in a specific range of
fields, but with the code I have below I am not quite sure of the
syntax to do this. I would like to specify the starting cell in Excel
for it to export to (IE B1 cell). Please if you need more information
or this isn't the forum for this let me know.

Dim strSQL As String
Dim dbD As DAO.Database
Dim qdfExport As DAO.QueryDef
Dim rscoordinator As DAO.Recordset
Dim strFileSpec As String

Set dbD = CurrentDb()
Set qdfExport = dbD.QueryDefs("qrytemp")
Set rscoordinator = dbD.OpenRecordset("Query2", dbOpenSnapshot)
Set rsemployee = dbD.OpenRecordset("Query2", dbOpenSnapshot)
Do Until rscoordinator.EOF
Do Until rsemployee.EOF


'qdfExport.SQL = "SELECT * FROM employee_coordinator WHERE
[coordinator]=""" & rscoordinator.Fields("coordinator").Value & " "";"
qdfExport.SQL = "SELECT * FROM employee_coordinator INNER JOIN data1 ON
employee_coordinator.emplid = data1.employeeid WHERE [coordinator] =
""" & rscoordinator.Fields("coordinator").Value & " "";"
qdfExport.SQL = "SELECT * FROM employee_coordinator INNER JOIN data1 ON
employee_coordinator.emplid = data1.employeeid WHERE [data1.employee] =
""" & rsemployee.Fields("employee").Value & " "";"

strFileSpec = "C:\1 corey projects\HEC\testing\" _
& rscoordinator.Fields("coordinator").Value & " " & Format(Date,
"mm-dd-yyyy") & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrytemp",
strFileSpec, True, " " & rsemployee.Fields("employee").Value
rscoordinator.MoveNext
rsemployee.MoveNext
Loop
Loop

Thanks

Ken,

Thanks for the article great reference. So if I am reading the
article correctly the name of the worksheet in the case of the code
above, is that the employee name would not be the same as the range
name IE B1. so it won't export starting in cell B1 ?

Is it possible to establish an Excel template and export to the
template within a specified starting cell? would this still have the
problems noted in your article?

What do you mean in this statement that I may need to use automation
and write directly to Excel file. Is there a better way for me to do
the above without using Transerspreadsheet?

You may need to use Automation and write directly into the EXCEL file if the
above options are not useful.

Thanks for your help I really appreciate it

Corky

Thanks for your help

 

[Ken Snell \(MVP\)]

Answers in line (way down at bottom):

--

Ken Snell
<MS ACCESS MVP>

See this article for information about limited capabilities of the Range
argument for DoCmd.TransferSpreadsheet method:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html

You may need to use Automation and write directly into the EXCEL file if
the
above options are not useful.

--

Ken Snell
<MS ACCESS MVP>

I am trying to export to Excel using docmd.transferspreadsheet within
an MS Access 2003 Form. The code below automatically exports by
coordinator to a specific workbook and the employees associated with
that coordinator into multiple sheets for each employee within that
workbook. I know you can start exporting in a specific range of
fields, but with the code I have below I am not quite sure of the
syntax to do this. I would like to specify the starting cell in Excel
for it to export to (IE B1 cell). Please if you need more information
or this isn't the forum for this let me know.

Dim strSQL As String
Dim dbD As DAO.Database
Dim qdfExport As DAO.QueryDef
Dim rscoordinator As DAO.Recordset
Dim strFileSpec As String

Set dbD = CurrentDb()
Set qdfExport = dbD.QueryDefs("qrytemp")
Set rscoordinator = dbD.OpenRecordset("Query2", dbOpenSnapshot)
Set rsemployee = dbD.OpenRecordset("Query2", dbOpenSnapshot)
Do Until rscoordinator.EOF
Do Until rsemployee.EOF


'qdfExport.SQL = "SELECT * FROM employee_coordinator WHERE
[coordinator]=""" & rscoordinator.Fields("coordinator").Value & " "";"
qdfExport.SQL = "SELECT * FROM employee_coordinator INNER JOIN data1 ON
employee_coordinator.emplid = data1.employeeid WHERE [coordinator] =
""" & rscoordinator.Fields("coordinator").Value & " "";"
qdfExport.SQL = "SELECT * FROM employee_coordinator INNER JOIN data1 ON
employee_coordinator.emplid = data1.employeeid WHERE [data1.employee] =
""" & rsemployee.Fields("employee").Value & " "";"

strFileSpec = "C:\1 corey projects\HEC\testing\" _
& rscoordinator.Fields("coordinator").Value & " " & Format(Date,
"mm-dd-yyyy") & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrytemp",
strFileSpec, True, " " & rsemployee.Fields("employee").Value
rscoordinator.MoveNext
rsemployee.MoveNext
Loop
Loop

Thanks

Ken,

Thanks for the article great reference. So if I am reading the
article correctly the name of the worksheet in the case of the code
above, is that the employee name would not be the same as the range
name IE B1. so it won't export starting in cell B1 ?

Not sure what you're asking. If you have a Range already established in the
workbook file that is named the same as the "name" you use as the Range
argument, and the Range in the file is more than one cell "big", then the
export will begin in the first (topleftmost) cell. If there is no Range in
the file, then ACCESS will create a new worksheet with your Range argument's
name, and a new Range in the file (Range will be named the same as the Range
argument that you provide).

Is it possible to establish an Excel template and export to the
template within a specified starting cell? would this still have the
problems noted in your article?

A template is seen as an existing EXCEL file by TransferSpreadsheet, so the
info provided will be the same as for the "existing file" information. If
you create a template file and create a Range in the file that defines where
you want the data to be put, then you can use TransferSpreadsheet in the
manner indicated in the article.

What do you mean in this statement that I may need to use automation
and write directly to Excel file. Is there a better way for me to do
the above without using Transerspreadsheet?

Automation is the term used for when VBA programming code is used to
actually manipulate EXCEL and do things in EXCEL files. You can establish an
EXCEL application instance, open a file, create a file, write to cells in
the file, etc. You use the EXCEL VBA object model to do this. It can be a
bit slow, and you need to know EXCEL VBA code. But, using it, you can create
a recordset of your data, open/create an EXCEL file, and then walk through
the recordset and write the data (one field at a time) to the sheet wherever
you want the data to go. I believe you also can use the CopyFromRecordset
method in EXCEL VBA when doing this, but I've not used this approach; I
prefer to write to the specific cells.

If you want to pursue this, post back and I'll get some generic code for
you.

 

[corkster]

Answers in line (way down at bottom):

--

Ken Snell
<MS ACCESS MVP>

See this article for information about limited capabilities of the Range
argument for DoCmd.TransferSpreadsheet method:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html

You may need to use Automation and write directly into the EXCEL file if
the
above options are not useful.

--

Ken Snell
<MS ACCESS MVP>

I am trying to export to Excel using docmd.transferspreadsheet within
an MS Access 2003 Form. The code below automatically exports by
coordinator to a specific workbook and the employees associated with
that coordinator into multiple sheets for each employee within that
workbook. I know you can start exporting in a specific range of
fields, but with the code I have below I am not quite sure of the
syntax to do this. I would like to specify the starting cell in Excel
for it to export to (IE B1 cell). Please if you need more information
or this isn't the forum for this let me know.

Dim strSQL As String
Dim dbD As DAO.Database
Dim qdfExport As DAO.QueryDef
Dim rscoordinator As DAO.Recordset
Dim strFileSpec As String

Set dbD = CurrentDb()
Set qdfExport = dbD.QueryDefs("qrytemp")
Set rscoordinator = dbD.OpenRecordset("Query2", dbOpenSnapshot)
Set rsemployee = dbD.OpenRecordset("Query2", dbOpenSnapshot)
Do Until rscoordinator.EOF
Do Until rsemployee.EOF


'qdfExport.SQL = "SELECT * FROM employee_coordinator WHERE
[coordinator]=""" & rscoordinator.Fields("coordinator").Value & " "";"
qdfExport.SQL = "SELECT * FROM employee_coordinator INNER JOIN data1 ON
employee_coordinator.emplid = data1.employeeid WHERE [coordinator] =
""" & rscoordinator.Fields("coordinator").Value & " "";"
qdfExport.SQL = "SELECT * FROM employee_coordinator INNER JOIN data1 ON
employee_coordinator.emplid = data1.employeeid WHERE [data1.employee] =
""" & rsemployee.Fields("employee").Value & " "";"

strFileSpec = "C:\1 corey projects\HEC\testing\" _
& rscoordinator.Fields("coordinator").Value & " " & Format(Date,
"mm-dd-yyyy") & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrytemp",
strFileSpec, True, " " & rsemployee.Fields("employee").Value
rscoordinator.MoveNext
rsemployee.MoveNext
Loop
Loop

Thanks

Ken,

Thanks for the article great reference. So if I am reading the
article correctly the name of the worksheet in the case of the code
above, is that the employee name would not be the same as the range
name IE B1. so it won't export starting in cell B1 ?

Not sure what you're asking. If you have a Range already established in the
workbook file that is named the same as the "name" you use as the Range
argument, and the Range in the file is more than one cell "big", then the
export will begin in the first (topleftmost) cell. If there is no Range in
the file, then ACCESS will create a new worksheet with your Range argument's
name, and a new Range in the file (Range will be named the same as the Range
argument that you provide).

Is it possible to establish an Excel template and export to the
template within a specified starting cell? would this still have the
problems noted in your article?

A template is seen as an existing EXCEL file by TransferSpreadsheet, so the
info provided will be the same as for the "existing file" information. If
you create a template file and create a Range in the file that defines where
you want the data to be put, then you can use TransferSpreadsheet in the
manner indicated in the article.

What do you mean in this statement that I may need to use automation
and write directly to Excel file. Is there a better way for me to do
the above without using Transerspreadsheet?

Automation is the term used for when VBA programming code is used to
actually manipulate EXCEL and do things in EXCEL files. You can establish an
EXCEL application instance, open a file, create a file, write to cells in
the file, etc. You use the EXCEL VBA object model to do this. It can be a
bit slow, and you need to know EXCEL VBA code. But, using it, you can create
a recordset of your data, open/create an EXCEL file, and then walk through
the recordset and write the data (one field at a time) to the sheet wherever
you want the data to go. I believe you also can use the CopyFromRecordset
method in EXCEL VBA when doing this, but I've not used this approach; I
prefer to write to the specific cells.

If you want to pursue this, post back and I'll get some generic code for
you.

Thanks for your help I really appreciate it

Corky

Thanks for your help

Ken

after rereading the document you sent me, it states that if the
worksheet doesn't exist it sounds like the following docmd will work
(which it does) I just need to get it to start export in cell b1. The
other issues are if workbook exists (which isn't the case here). All
of the workbooks and worksheets within the workbook will be recreated
everytime the code runs (which means the workbooks and sheets won't
exist when code is run). The files that are etablished from the code
will be moved to another directory.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrytemp",
strFileSpec, True, " " & rsemployee.Fields("employee").Value

The & rsemployee.fields(employee).value populates each sheet in the
coordinator workbook with the employee name. In the document it states
the export starts in A1 cell.

I have tried the following

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrytemp",
strFileSpec, True, "B1" & rsemployee.Fields("employee").Value

Which doesn't work of course

Do you have a suggestion for the syntax that might work and how to
export to a template? (that is if I am correct on what I read in your
article see excerpt from the article
EXCEL FILE DOES NOT ALREADY EXIST
This range will include the field names that are
exported as the first row of data, and the range will begin in cell A1.
(I would like to export to cell b1


I would also be interested in the Excel VBA code just to learn more or
for possible other solutions.
Thanks again

corky

 

[Ken Snell \(MVP\)]

Sorry, but I do not know of a way to use TransferSpreadsheet to export to a
brand new (didn't exist until TransferSpreadsheet creates it) EXCEL file and
have the data begin in a cell other than A1.

As I noted earlier, you can use VBA code to make a copy of a template file,
name that file what you want, and then export to the copied file. If the
template file has a Range created in it that begins in cell B1 on a sheet
that exists in the file, using that Range name in your export will cause the
export to begin in cell B1. Note that you cannot avoid having the field
names be the first row of data that are put in the spreadsheet if you use
TransferSpreadsheet.

As for how to Automate EXCEL, here is some generic code that will get you
started:

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\Filename.xls")
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1") ' this is the first cell into which data go
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbReadOnly)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
' if you want to write field names in the first row:
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
Next lngColumn
' write data to worksheet
Do While rst.EOF = False
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
Next lngColumn
rst.MoveNext
Set xlc = xlc.Offset(1,0)
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Close True
Set xlw = Nothing
xlx.Quit

 

[corkster]

Ken,

Thanks for your help. I would have gone round and round trying to get
it to work. If you don't know of a way then it can't be done .
Thanks for the code I will play with that.

 

[Ken Snell \(MVP\)]

Found a small error in my generic code -- neglected to include moving to new
row in EXCEL if you write the field names. Here is corrected code:

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\Filename.xls")
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1") ' this is the first cell into which data go
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbReadOnly)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
' if you want to write field names in the first row:
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
Next lngColumn
Set xlc = xlc.Offset(1,0)
' write data to worksheet
Do While rst.EOF = False
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
Next lngColumn
rst.MoveNext
Set xlc = xlc.Offset(1,0)
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Close True
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing


Also, FYI, the above code is not written for "efficient" running, as it
resets the Range object for each new row. Using Offset with a counter
variable is a much faster way to do that. But the above code should give you
a good idea of the looping technique.

 

[corkster]

Ken,

Thanks for the new code. I will try using it this week hopefully. I
appreciate all of your help.

Take care