Access to Excel (2k)

Topic

New Reply

I have muddled through some access to word data transfers where I plop data from access to a word table. I would like to try this with Excel. Is there a way to get data to appear in a specific cell within excel. I use bookmarks to do the transfer to word and imagine the process would be similar. Is there anything different that I might need to know about?

Thank You

Reply | Quote

Replies

There are several ways to accomplish a transfer to Excel. One is a simple cut and paste to a specific cell or range. Another is to use the Export option on the menu to do the trick. A third way is to use the TransferSpreadsheet command either as a macro or in VBA, though in that case, you cannot specify a specific cell in Excel. You can also do the trick using Automation from Access to populate specific cells, create charts, text boxes, or anything you can do in Excel. Finally, you could link to the Excel workbook, making it appear as a table – but in at least some situations you may not be able to update the Excel workbook. If you decide to try one of these approaches and encounter problems, please post back here and we’ll try to help.

Reply | Quote

Thank You, I am going to try to use automation. When I do this with word, I usually create a query of the data I am going to transfer to word then with a word macro within a template grab each piece of data and plop in a table or within text using bookmarks. If I were to try this with Excel, I think(?) that I would just need to include the cell reference in the macro to drop in the data. Does this sound doable? The macro tends to be long though.

Thanks
Kevin

Reply | Quote

As Hans points out, you can use automation from Access, but you can also use automation from Excel to manipulate the Access object if you are more comfortable using VBA with Excel. As Hans example indicates, it is a fairly lengthy piece of code to do that trick, but we do it pretty regularly and it works quite well if you follow all of the guidelines for using Automation.

Reply | Quote

Thank You both

Very helpful.

Kevin

Reply | Quote

Thank You both

Very helpful.

Kevin

Reply | Quote

As Hans points out, you can use automation from Access, but you can also use automation from Excel to manipulate the Access object if you are more comfortable using VBA with Excel. As Hans example indicates, it is a fairly lengthy piece of code to do that trick, but we do it pretty regularly and it works quite well if you follow all of the guidelines for using Automation.

Reply | Quote

Thank You, I am going to try to use automation. When I do this with word, I usually create a query of the data I am going to transfer to word then with a word macro within a template grab each piece of data and plop in a table or within text using bookmarks. If I were to try this with Excel, I think(?) that I would just need to include the cell reference in the macro to drop in the data. Does this sound doable? The macro tends to be long though.

Thanks
Kevin

Reply | Quote

There are several ways to accomplish a transfer to Excel. One is a simple cut and paste to a specific cell or range. Another is to use the Export option on the menu to do the trick. A third way is to use the TransferSpreadsheet command either as a macro or in VBA, though in that case, you cannot specify a specific cell in Excel. You can also do the trick using Automation from Access to populate specific cells, create charts, text boxes, or anything you can do in Excel. Finally, you could link to the Excel workbook, making it appear as a table – but in at least some situations you may not be able to update the Excel workbook. If you decide to try one of these approaches and encounter problems, please post back here and we’ll try to help.

Reply | Quote

If DoCmd.TransferSpreadsheet (to export an entire table or query to Excel) doesn’t suit your purpose, you can use Automation, just like you did with Word. You can write values to specific cells, you can refer to them by their cell address. Instead of with Document objects, you are working with Workbook and Worksheet objects; cells and ranges are represented by the Range object.

You must set a reference to the Microsoft Excel 9.0 Object Library.

Example:

Sub Export2XL()
Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlWsh As Excel.Worksheet
Dim blnStartExcel As Boolean

On Error Resume Next

Set xlApp = GetObject(, “Excel.Application”)
If xlApp Is Nothing Then
Set xlApp = CreateObject(“Excel.Application”)
If xlApp Is Nothing Then
MsgBox “Cannot open Excel.”, vbCritical
Exit Sub
End If
blnStartExcel = True
End If

On Error GoTo ErrHandler

Set xlWbk = xlApp.Workbooks.Add
Set xlWsh = xlWbk.Worksheets(1)

xlWsh.Range(“A1”) = …
…

xlWbk.SaveAs “C:ExcelTest.xls”

ExitHandler:
On Error Resume Next
Set xlWsh = Nothing
xlWbk.Close SaveChanges:=False
Set xlWbk = Nothing
If blnStartExcel Then
xlApp.Quit
End If
Set xlApp = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Reply | Quote

If DoCmd.TransferSpreadsheet (to export an entire table or query to Excel) doesn’t suit your purpose, you can use Automation, just like you did with Word. You can write values to specific cells, you can refer to them by their cell address. Instead of with Document objects, you are working with Workbook and Worksheet objects; cells and ranges are represented by the Range object.

You must set a reference to the Microsoft Excel 9.0 Object Library.

Example:

Sub Export2XL()
Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlWsh As Excel.Worksheet
Dim blnStartExcel As Boolean

On Error Resume Next

Set xlApp = GetObject(, “Excel.Application”)
If xlApp Is Nothing Then
Set xlApp = CreateObject(“Excel.Application”)
If xlApp Is Nothing Then
MsgBox “Cannot open Excel.”, vbCritical
Exit Sub
End If
blnStartExcel = True
End If

On Error GoTo ErrHandler

Set xlWbk = xlApp.Workbooks.Add
Set xlWsh = xlWbk.Worksheets(1)

xlWsh.Range(“A1”) = …
…

xlWbk.SaveAs “C:ExcelTest.xls”

ExitHandler:
On Error Resume Next
Set xlWsh = Nothing
xlWbk.Close SaveChanges:=False
Set xlWbk = Nothing
If blnStartExcel Then
xlApp.Quit
End If
Set xlApp = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Reply | Quote