Printable View
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.
Here's one simple way of doing it:
Good luck!Code: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
Actually, Joacim, I found a much easier way of doing it:
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...Code: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
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.
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...
Coox, how can we specify which "Sheet" (in the excel file) is to be used to copy the data?
Thanks, :)
Rowena - do you mean which sheet you want the data to go to? Or do you just want to rename the sheet? Please elucidate... :)
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! :)
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:
Good luck... please post here if you find how to just specify a sheet without naming a range first...Code: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