Exporting Each Record to Separate Excel File

  • Thread starter Thread starter blg
  • Start date Start date
B

blg

I have an Access table with approx. 2,500 records.
I want to export each record into its own Excel file that is named by the
key field.
Is there a way to automate this process in Access?
 
Try something like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim qdfCurr As DAO.QueryDef
Dim strFileName As String
Dim strFolder As String
Dim strSQL As String

strFolder = "C:\SomeFolderName\"

Set dbCurr = CurrentDb
' Change the names in the SQL
Set rsCurr = CurrentDb.OpenRecordset _
("SELECT KeyField FROM MyTable")
Do While rsCurr.EOF = False
' Change the names in the SQL
strSQL = "SELECT KeyField, Field1, Field2, Field3 " & _
"FROM MyTable " & _
"WHERE KeyField = " & rs.KeyField
strFileName = KeyField & ".xls"
' Make sure the file doesn't already exist
If Len(Dir(strFolder & strFileName)) > 0 Then
Kill strFolder & strFileName
End If
' qryTemp must already exist.
Set qdfCurr = dbCurr.QueryDefs("qryTemp")
qdfCurr.SQL = strSQL
qdfCurr.Close
DoCmd.TransferSpreadsheet acExport, , , _
"qryTemp", strFolder & strFileName
rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing
 
I will give this a try. Thanks


--
blg


Douglas J. Steele said:
Try something like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim qdfCurr As DAO.QueryDef
Dim strFileName As String
Dim strFolder As String
Dim strSQL As String

strFolder = "C:\SomeFolderName\"

Set dbCurr = CurrentDb
' Change the names in the SQL
Set rsCurr = CurrentDb.OpenRecordset _
("SELECT KeyField FROM MyTable")
Do While rsCurr.EOF = False
' Change the names in the SQL
strSQL = "SELECT KeyField, Field1, Field2, Field3 " & _
"FROM MyTable " & _
"WHERE KeyField = " & rs.KeyField
strFileName = KeyField & ".xls"
' Make sure the file doesn't already exist
If Len(Dir(strFolder & strFileName)) > 0 Then
Kill strFolder & strFileName
End If
' qryTemp must already exist.
Set qdfCurr = dbCurr.QueryDefs("qryTemp")
qdfCurr.SQL = strSQL
qdfCurr.Close
DoCmd.TransferSpreadsheet acExport, , , _
"qryTemp", strFolder & strFileName
rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing
Click to expand...
 
I would like to try to do this using Visual Basic 2008 Express edition.

I am relatively new to VB. How can I adapt this code to VB 2008?
 
Back
Top