hindersaliva
asked on
Export Access table as CSV (from Excel)
I need to fire off a command from Excel 2016 to export tables (as CSV) from an Access 2016 database.
The samples I have seen are DAO. (DoCmd)
I already have an ADO connection in my Excel app. Can I use that?
Sample code would help. Thanks.
Steps:
List the table names on a sheet
Loop through. Export each table to a CSV file with filename as table name
The samples I have seen are DAO. (DoCmd)
I already have an ADO connection in my Excel app. Can I use that?
Sample code would help. Thanks.
Steps:
List the table names on a sheet
Loop through. Export each table to a CSV file with filename as table name
do you have any reason why you want to do this in Excel and not in Access?
ASKER
Rae, I've developed a teamwork Excel application that stores its data in Access. I'd like keep all the functionality within the Excel UI. So far there is no reason for the users to go anywhere near the Access database. I'd like to keep the database 'hands off'.
<I already have an ADO connection in my Excel app. Can I use that? > Yes
can you post the codes?
can you post the codes?
ASKER
Public objConn As New ADODB.Connection
Sub DBConnectionAccess()
If CBool(objConn.State And adStateOpen) Then objConn.Close
Dim strPathToDB As String
On Error GoTo ErrHandler:
'set path according to live status
If Range("Site").Value = "Office" Then
strPathToFolder = Range("PathToFolderOffice").Value
End If
If Range("Site").Value = "Developer" Then
strPathToFolder = Range("PathToFolderDev").Value
End If
If Range("LiveStatus").Value = "LIVE" Then
strPathToDB = strPathToFolder + "\Database\VolMan.accdb"
End If
If Range("LiveStatus").Value = "Training" Or Range("LiveStatus").Value = "Dev" Then
strPathToDB = strPathToFolder + "\Database\Training\VolManTraining.accdb"
End If
'MsgBox strPathToDB
'open connection to Access database
objConn.Open "Provider = Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strPathToDB
Exit Sub
ErrHandler:
' error handling code
MsgBox "No connection to the VolMan database!" & vbCrLf & "Please call support"
End Sub
Sub DBConnectionClose()
If CBool(objConn.State And adStateOpen) Then objConn.Close
Set objConn = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Helen. That's brilliant. I've been a big fan of your work over the years!
One thing. I'm developing on Excel and Access 2016. The users are on 2010. I realise that, having Referenced the Access 2016 library it won't work on the 2010. I understand I would need late binding. What's the modification to make it 'late binding' please?
Thanks.
One thing. I'm developing on Excel and Access 2016. The users are on 2010. I realise that, having Referenced the Access 2016 library it won't work on the 2010. I understand I would need late binding. What's the modification to make it 'late binding' please?
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Helen. Thanks Rey. I adapted the code from both of you. Perfect.
ASKER
Rey. I got from you how to do the Late Binding.
The rest of the code didn't run for some reason. It seemed to be looking for the .CSV file and not finding it there.
I used Helen's method of firing off the code in Access from Excel.
This is my final result (in Excel)
The rest of the code didn't run for some reason. It seemed to be looking for the .CSV file and not finding it there.
I used Helen's method of firing off the code in Access from Excel.
This is my final result (in Excel)
Public Sub ExportAccessTables_LATEBINDING()
'Created by Helen Feddema 28-Jan-2017
'Last modified by Helen Feddema 28-Jan-2017
'Adapted Late Binding code from Rey Obrero on same Q on my E-E
On Error GoTo ErrorHandler
Dim strDBNameAndPath As String
Dim appAccess As Object
Set appAccess = CreateObject("Access.Application")
'set path according to Site and Live Status
If Range("Site").Value = "Office" Then
strPathToFolder = Range("PathToFolderOffice").Value
End If
If Range("Site").Value = "Developer" Then
strPathToFolder = Range("PathToFolderDev").Value
End If
'VolMan
strDBNameAndPath = strPathToFolder + "\Database\VolMan.accdb"
appAccess.OpenCurrentDatabase FilePath:=strDBNameAndPath, _
exclusive:=False
appAccess.Run "ExportTables"
appAccess.CloseCurrentDatabase
'VolMan_Log
strDBNameAndPath = strPathToFolder + "\Database\VolMan_Log.accdb"
appAccess.OpenCurrentDatabase FilePath:=strDBNameAndPath, _
exclusive:=False
appAccess.Run "ExportTables"
appAccess.CloseCurrentDatabase
MsgBox "Done!"
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in ExportAccessTables procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
You got it -- just declare appAccess as Object, and use CreateObject. It is such a pain to have to get code to run in multiple Office versions, and sometimes it just can't be done, because of shared objects, especially when using more than one Office component. Good thing that hasn't happened here!