D
Don
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\Tools\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
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\Tools\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