Export Jet Table to Excel worksheet

[coox]

Having a spot of bother (or rather a right load of old stress and grief) exporting a table to excel.

I can get VB6 to open Access, and even get the right db open (using OpenCurrentDatabase) but can't seem to figure out how to select a table and then export that table to an Excel 97 workbook. Would be awfully grateful, and I'd give you ten points and a gold star thing.

[Joacim Andersson]

Here's one simple way of doing it:

Dim fld As Field
Dim sText As String
Dim rs As Recordset

Set rs = YourDBObject.OpenRecordset("TableName", dbOpenSnapShot)
Do While Not rs.EOF
    For Each fld In rs.Fields
        sText = sText & fld.Value & vbTab
    Next
    sText = sText & vbCrLf
    rs.MoveNext
Loop
On Error Resume Next
Set xl = GetObject(, "Excel.Application")
If Err Then
    Set xl = CreateObject("Excel.Application")
End If
Clipboard.Clear
Clipboard.SetText sText
xl.Workbooks.Add
xl.Visible = True
xl.Range("A1").Select
xl.ActiveSheet.Paste

Good luck!

[coox]

Actually, Joacim, I found a much easier way of doing it:

Sub ExportToExcel()
 Dim myAccess As Access.Application
'**********************************
 Set myAccess = CreateObject("Access.Application")
 With myAccess
  .Visible = False
  .OpenCurrentDatabase ("C:\Data\UKData.mdb")
  .DoCmd.TransferSpreadsheet acExport, 8, "IMP", "C:\Windows\Desktop\Export.xls", True
  .Quit
 End With
 Set myAccess = Nothing
 MsgBox "Exported OK", vbInformation, "Export"
End Sub

The only bummer with this method is that I have to include the Access 8 Object Library with my project, so it gets even bigger to distribute...

[Joacim Andersson]

Just because you set a reference to the library doesn't mean you have to distribute it.
If the end-user doesn't have Access they can't run the code. If you don't include the DAO library.

[coox]

Sorry Joacim, yeah I meant to say that - very few people in our organisation have Access on their machines, so I will have to distribute the object library...

Cheers, for your help though...

[rowena]

Coox, how can we specify which "Sheet" (in the excel file) is to be used to copy the data?

Thanks,

[coox]

Rowena - do you mean which sheet you want the data to go to? Or do you just want to rename the sheet? Please elucidate...

[rowena]

Yes Coox, which sheet I want to go the data. Actually, I am doing the reverse side. I am importing my excel data to a particular table in Access. However, my excel file has 5 sheets and I just want to transfer Sheet#5 to my table in Access. Is this possible? I hope so.

Thanks a lot!

[coox]

Rowena,

Right, so far I can't find a way of specifying just the name of a sheet - the method I used just seems to plump for the first spreadsheet - but I did find a way around it - just NAME the range on the sheet you want to import, eg, go to Sheet5, select the whole area to import, and then key a name in the Name Box (top left).

Anyway, here's some code that works:

Private Sub Command1_Click()
 Dim myAccess As Access.Application
'**********************************
 Set myAccess = CreateObject("Access.Application")
 With myAccess
  .Visible = False
  
  .OpenCurrentDatabase ("C:\Windows\Desktop\MyOldDb.mdb") 'Either pick an existing DB or...
  .NewCurrentDatabase ("C:\Windows\Desktop\MyNewDb.mdb") '  ...make a new one
  
  
  '"MyTable" can be an existing one or a new one
  .DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "MyTable", "C:\Windows\Desktop\MySpreadSheet.xls", , "MyNamedRange"
  .Quit
 End With
 Set myAccess = Nothing
 MsgBox "Imported OK", vbInformation, "Import"
End Sub

Good luck... please post here if you find how to just specify a sheet without naming a range first...