Help with my code Exporting to multiple excel spreadsheets

A

AirgasRob

First I am trying to export a table to multiple excel spreadsheets. In my
table I have a column called [loc] for locations. For each group of records
with the same [loc] i want to export to excel for that group. I have figured
out how to export excel spreadsheets for each group of locations [loc] but
not how to filter each spreadhsheet to only show that location in it. Any
help would be appreciated and to the person who originally posted this
code...Thank you! Also is there a way to do this without having to reference
DAO?

Public Sub CreateWorksheets()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strPath As String

Set dbs = CurrentDb
On Error Resume Next
dbs.QueryDefs.Delete ("qryloc")
On Error GoTo 0
Set qdf = dbs.CreateQueryDef("qryloc")

Set rst = dbs.OpenRecordset("SELECT DISTINCT [loc] " & _
"FROM [TblMain]")

With rst
Do While Not .EOF
strSQL = "SELECT * FROM [TblMain] WHERE [loc] = """ & _
![Loc] & """"
qdf.SQL = strSQL
strPath = "C:\TransferStation\ExportExcel\ " & _
![Loc] & ".xls"
On Error Resume Next
Kill strPath
On Error GoTo 0
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
TableName:="TblMain", _
FileName:=strPath
.MoveNext
Loop
.Close
End With

Set rst = Nothing
Set dbs = Nothing

End Sub
 
R

Ralph

You are exporting the entire table each time, export the query instead.

DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
TableName:="qryLoc", _
FileName:=strPath

Would you rather use ADODB rather than DAO?
Do you know Ed Deely?
 
A

AirgasRob

I have nothing against using DAO I just dont want my users to have to install
the relationship for it on each of there PC's.

That name does ring a bell but off the top of my head I am not sure if I do.

Ralph said:
You are exporting the entire table each time, export the query instead.

DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
TableName:="qryLoc", _
FileName:=strPath

Would you rather use ADODB rather than DAO?
Do you know Ed Deely?

AirgasRob said:
First I am trying to export a table to multiple excel spreadsheets. In my
table I have a column called [loc] for locations. For each group of records
with the same [loc] i want to export to excel for that group. I have figured
out how to export excel spreadsheets for each group of locations [loc] but
not how to filter each spreadhsheet to only show that location in it. Any
help would be appreciated and to the person who originally posted this
code...Thank you! Also is there a way to do this without having to reference
DAO?

Public Sub CreateWorksheets()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strPath As String

Set dbs = CurrentDb
On Error Resume Next
dbs.QueryDefs.Delete ("qryloc")
On Error GoTo 0
Set qdf = dbs.CreateQueryDef("qryloc")

Set rst = dbs.OpenRecordset("SELECT DISTINCT [loc] " & _
"FROM [TblMain]")

With rst
Do While Not .EOF
strSQL = "SELECT * FROM [TblMain] WHERE [loc] = """ & _
![Loc] & """"
qdf.SQL = strSQL
strPath = "C:\TransferStation\ExportExcel\ " & _
![Loc] & ".xls"
On Error Resume Next
Kill strPath
On Error GoTo 0
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
TableName:="TblMain", _
FileName:=strPath
.MoveNext
Loop
.Close
End With

Set rst = Nothing
Set dbs = Nothing

End Sub
Click to expand...
Click to expand...
 
R

Ralph

The DAO reference should automatically be set in an Access database.

AirgasRob said:
I have nothing against using DAO I just dont want my users to have to install
the relationship for it on each of there PC's.

That name does ring a bell but off the top of my head I am not sure if I do.

Ralph said:
You are exporting the entire table each time, export the query instead.

DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
TableName:="qryLoc", _
FileName:=strPath

Would you rather use ADODB rather than DAO?
Do you know Ed Deely?

AirgasRob said:
First I am trying to export a table to multiple excel spreadsheets. In my
table I have a column called [loc] for locations. For each group of records
with the same [loc] i want to export to excel for that group. I have figured
out how to export excel spreadsheets for each group of locations [loc] but
not how to filter each spreadhsheet to only show that location in it. Any
help would be appreciated and to the person who originally posted this
code...Thank you! Also is there a way to do this without having to reference
DAO?

Public Sub CreateWorksheets()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strPath As String

Set dbs = CurrentDb
On Error Resume Next
dbs.QueryDefs.Delete ("qryloc")
On Error GoTo 0
Set qdf = dbs.CreateQueryDef("qryloc")

Set rst = dbs.OpenRecordset("SELECT DISTINCT [loc] " & _
"FROM [TblMain]")

With rst
Do While Not .EOF
strSQL = "SELECT * FROM [TblMain] WHERE [loc] = """ & _
![Loc] & """"
qdf.SQL = strSQL
strPath = "C:\TransferStation\ExportExcel\ " & _
![Loc] & ".xls"
On Error Resume Next
Kill strPath
On Error GoTo 0
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
TableName:="TblMain", _
FileName:=strPath
.MoveNext
Loop
.Close
End With

Set rst = Nothing
Set dbs = Nothing

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access to Excel Automation Way over my head. 1
Access to Outlook Automation (Hopefully Easy Answer) 2
Working outlook via vba code with xlsx - error 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Exporting multiple Excel spreadhsheets using Excel templates 8
VBA Code Help 3
Export data to multiple excel spreadsheets 2
Send an Query output with a Email using "sendObject" function 1

Top