Export Access Table to Excel
 

Hi,

I'm trying to use code that I found in a previous post to export an
Access table to Excel.

The code is as follows:

Private Sub Command1_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Excel As Object
Dim wb As Object
Dim ws As Object
Dim recArray As Variant
Dim tableName As String
Dim strSQL As String
Dim RecCount As Long
Dim iCols As Integer, iRows As Integer, fldCount As Integer


'Create object variable referring to the open database
Set db = CurrentDb
'Create object variable referring to the SalesData table
tableName = "tblInventories"


strSQL = "Select * from " & tableName 'Recordset to copy


Set Excel = CreateObject("Excel.Application")
Set rst = CurrentDb.OpenRecordset(strSQL)
'Open new workbook and create object variable reference to it
Set wb =
Excel.Workbooks.Open("\\Druma701va16100\forestry\T ools\Databases\Forested
NRMU'S\Inventories.xls")
'Create object variable refering to worksheet
Set ws = wb.Worksheets("New")


'Show Excel and maximise its window
Excel.Visible = True
ws.Activate
'Transfer field names to worksheet
fldCount = rst.Fields.Count
For iCols = 0 To fldCount - 1
ws.Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
Next
'Find number of records
With rst
.MoveLast
RecCount = .RecordCount
.MoveFirst
End With


'Transfer data
recArray = rst.GetRows(RecCount)
ws.Range(ws.Cells(2, 1), _
ws.Cells(RecCount + 1, fldCount)).Value =
Excel.Transpose(recArray)
''Format worksheet
With ws.Range(ws.Cells(1, 1), ws.Cells(1, fldCount))
.Font.Bold = True
.EntireColumn.AutoFit
End With
'The following code could be used to save the workbook and Quit
Excel
wb.Close SaveChanges:=True
Excel.Quit
'Release object variables
Set db = Nothing
Set rst = Nothing
Set Excel = Nothing
Set wb = Nothing
Set ws = Nothing


End Sub

It is exporting the field names to Excel properly. However, at the
following line, I get a Type Mismatch error:

ws.Range(ws.Cells(2, 1), _
ws.Cells(RecCount + 1, fldCount)).Value =
Excel.Transpose(recArray)


Can anybody help? I would sincerely appreciate any input.

Thanks in advance,
Don

Export Access Table to Excel
 

I'm so sorry. Again, I have posted prematurely. I modified the code
below to incorporate code from the MKB at
http://support.microsoft.com/kb/246335/en-us.

Don wrote:
Hi,

I'm trying to use code that I found in a previous post to export an
Access table to Excel.

The code is as follows:

Private Sub Command1_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Excel As Object
Dim wb As Object
Dim ws As Object
Dim recArray As Variant
Dim tableName As String
Dim strSQL As String
Dim RecCount As Long
Dim iCols As Integer, iRows As Integer, fldCount As Integer


'Create object variable referring to the open database
Set db = CurrentDb
'Create object variable referring to the SalesData table
tableName = "tblInventories"


strSQL = "Select * from " & tableName 'Recordset to copy


Set Excel = CreateObject("Excel.Application")
Set rst = CurrentDb.OpenRecordset(strSQL)
'Open new workbook and create object variable reference to it
Set wb =
Excel.Workbooks.Open("\\Druma701va16100\forestry\T ools\Databases\Forested
NRMU'S\Inventories.xls")
'Create object variable refering to worksheet
Set ws = wb.Worksheets("New")


'Show Excel and maximise its window
Excel.Visible = True
ws.Activate
'Transfer field names to worksheet
fldCount = rst.Fields.Count
For iCols = 0 To fldCount - 1
ws.Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
Next
'Find number of records
With rst
.MoveLast
RecCount = .RecordCount
.MoveFirst
End With


'Transfer data
recArray = rst.GetRows(RecCount)
ws.Range(ws.Cells(2, 1), _
ws.Cells(RecCount + 1, fldCount)).Value =
Excel.Transpose(recArray)
''Format worksheet
With ws.Range(ws.Cells(1, 1), ws.Cells(1, fldCount))
.Font.Bold = True
.EntireColumn.AutoFit
End With
'The following code could be used to save the workbook and Quit
Excel
wb.Close SaveChanges:=True
Excel.Quit
'Release object variables
Set db = Nothing
Set rst = Nothing
Set Excel = Nothing
Set wb = Nothing
Set ws = Nothing


End Sub

It is exporting the field names to Excel properly. However, at the
following line, I get a Type Mismatch error:

ws.Range(ws.Cells(2, 1), _
ws.Cells(RecCount + 1, fldCount)).Value =
Excel.Transpose(recArray)


Can anybody help? I would sincerely appreciate any input.

Thanks in advance,
Don

Export Access Table to Excel
 

Don,
Additionally, it may not be a good idea to "Excel" as the name of your
Excel.Application object variable.
Use something like xlApp for eample that will not lead to confusion.

NickHK

"Don"
egroups.com...
I'm so sorry. Again, I have posted prematurely. I modified the code
below to incorporate code from the MKB at
http://support.microsoft.com/kb/246335/en-us.

Don wrote:
Hi,

I'm trying to use code that I found in a previous post to export an
Access table to Excel.

The code is as follows:

Private Sub Command1_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Excel As Object
Dim wb As Object
Dim ws As Object
Dim recArray As Variant
Dim tableName As String
Dim strSQL As String
Dim RecCount As Long
Dim iCols As Integer, iRows As Integer, fldCount As Integer


'Create object variable referring to the open database
Set db = CurrentDb
'Create object variable referring to the SalesData table
tableName = "tblInventories"


strSQL = "Select * from " & tableName 'Recordset to copy


Set Excel = CreateObject("Excel.Application")
Set rst = CurrentDb.OpenRecordset(strSQL)
'Open new workbook and create object variable reference to it
Set wb =
Excel.Workbooks.Open("\\Druma701va16100\forestry\T ools\Databases\Forested
NRMU'S\Inventories.xls")
'Create object variable refering to worksheet
Set ws = wb.Worksheets("New")


'Show Excel and maximise its window
Excel.Visible = True
ws.Activate
'Transfer field names to worksheet
fldCount = rst.Fields.Count
For iCols = 0 To fldCount - 1
ws.Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
Next
'Find number of records
With rst
.MoveLast
RecCount = .RecordCount
.MoveFirst
End With


'Transfer data
recArray = rst.GetRows(RecCount)
ws.Range(ws.Cells(2, 1), _
ws.Cells(RecCount + 1, fldCount)).Value =
Excel.Transpose(recArray)
''Format worksheet
With ws.Range(ws.Cells(1, 1), ws.Cells(1, fldCount))
.Font.Bold = True
.EntireColumn.AutoFit
End With
'The following code could be used to save the workbook and Quit
Excel
wb.Close SaveChanges:=True
Excel.Quit
'Release object variables
Set db = Nothing
Set rst = Nothing
Set Excel = Nothing
Set wb = Nothing
Set ws = Nothing


End Sub

It is exporting the field names to Excel properly. However, at the
following line, I get a Type Mismatch error:

ws.Range(ws.Cells(2, 1), _
ws.Cells(RecCount + 1, fldCount)).Value =
Excel.Transpose(recArray)


Can anybody help? I would sincerely appreciate any input.

Thanks in advance,
Don