export to Excel From Access Database

[ielmrani via AccessMonster.com]

Hi All,
I have the following table that I would like to export to Excel:

Town Name

Bellmore John
Freeport Paul
Bellmore Ismail
Merrick Bob
Merrick Alan
Freeport Mike
Merrick Susan
Bellmore Andria
Bellmore Paula
Freeport Sunita

I have no problem exporting the data to an excel sheet, but when the data is
exported it's all in one tab. What I would help with is the following:

when the file is exported I would like to see the towns in seperate tabs.
tab one for Bellmore and the list of name, tab 2 Freeport and the list of
names...etc...
I hope this is clear.

Thanks in advance...

 

[John Nurick]

To do this it's necessary to construct and export a query for each town.
This can be done with VBA code: how are your programming skills?

 

[ielmrani via AccessMonster.com]

Thanks for replying. Are saying that I have to export seperate queries? One
query for each town?I am very familier with vba coding.

Ismail

To do this it's necessary to construct and export a query for each town.
This can be done with VBA code: how are your programming skills?

Hi All,
I have the following table that I would like to export to Excel:

[quoted text clipped - 21 lines]

Thanks in advance...

 

[John Nurick]

Hi Ismail,

I'd do it like this. BTW, it's best not to use "Name" or the names of
other common functions and properties as field names: sooner or later
they'll cause problems. I've replaced "Name" with "FirstName".

1) Create a query qryTowns that returns a sorted list of towns (SELECT
DISTINCT Town FROM MyTable ORDER BY Town.

2) Create a second select query qryExport. Your code will modify this,
so it doesn't matter what it does.

3) Build a VBA procedure like this:

Dim dbD as DAO.Database
Dim rsTowns AS DAO.Recordset
Dim strSQL AS String
Dim FileSpec As String

FileSpec = "D:\Folder\MyFile.xls"

Set dbD = CurrentDB()
Set rsTowns = dbD.OpenRecordset("qryTowns")

Do Until rsTowns.EOF
'Build SQL statement to return names for current town
strSQL = "SELECT FirstName FROM MyTable WHERE Town ='" _
& rsTowns.Fields("Town").Value _
& "' ORDER BY FirstName;"

'Assign to query
dbD.QueryDefs("qryExport").SQL = strSQL

'Export, passing the sheet name in the Range argument
docmd.TransferSpreadsheet acExport, , "qryExport", _
FileSpec, True, rsTowns.Fields("Town").Value
Loop

Thanks for replying. Are saying that I have to export seperate queries? One
query for each town?I am very familier with vba coding.

Ismail

To do this it's necessary to construct and export a query for each town.
This can be done with VBA code: how are your programming skills?

Hi All,
I have the following table that I would like to export to Excel:

[quoted text clipped - 21 lines]

Thanks in advance...

 

[ielmrani via AccessMonster.com]

Hi John,
I did everything you said. However, I am getting the following message:
Run time error '3436'
Failure creating file

and the following is highlighed:
DoCmd.TransferSpreadsheet acExport, , "qryExport", _
FileSpec, True, rsTowns.Fields("Town").Value

I tried running the procedure by hitting F5. How do you call a procedure?

Thanks so much.

Ismail

Hi Ismail,

I'd do it like this. BTW, it's best not to use "Name" or the names of
other common functions and properties as field names: sooner or later
they'll cause problems. I've replaced "Name" with "FirstName".

1) Create a query qryTowns that returns a sorted list of towns (SELECT
DISTINCT Town FROM MyTable ORDER BY Town.

2) Create a second select query qryExport. Your code will modify this,
so it doesn't matter what it does.

3) Build a VBA procedure like this:

Dim dbD as DAO.Database
Dim rsTowns AS DAO.Recordset
Dim strSQL AS String
Dim FileSpec As String

FileSpec = "D:\Folder\MyFile.xls"

Set dbD = CurrentDB()
Set rsTowns = dbD.OpenRecordset("qryTowns")

Do Until rsTowns.EOF
'Build SQL statement to return names for current town
strSQL = "SELECT FirstName FROM MyTable WHERE Town ='" _
& rsTowns.Fields("Town").Value _
& "' ORDER BY FirstName;"

'Assign to query
dbD.QueryDefs("qryExport").SQL = strSQL

'Export, passing the sheet name in the Range argument
docmd.TransferSpreadsheet acExport, , "qryExport", _
FileSpec, True, rsTowns.Fields("Town").Value
Loop

Thanks for replying. Are saying that I have to export seperate queries? One
query for each town?I am very familier with vba coding.

[quoted text clipped - 14 lines]

 

[John Nurick]

Hi Ismail,

The only way I've been actually able to generate this error is by
passing a filespec containing invalid characters, though the message
implies that it might also come from a problem with folder permissions
or network connections.

So the first thing to do is to check the value of FileSpec and ensure
that it points to a folder where you have full
(read/write/execute/delete) access and specifies a valid filename.

Hi John,
I did everything you said. However, I am getting the following message:
Run time error '3436'
Failure creating file

and the following is highlighed:
DoCmd.TransferSpreadsheet acExport, , "qryExport", _
FileSpec, True, rsTowns.Fields("Town").Value

I tried running the procedure by hitting F5. How do you call a procedure?

Thanks so much.

Ismail

Hi Ismail,

I'd do it like this. BTW, it's best not to use "Name" or the names of
other common functions and properties as field names: sooner or later
they'll cause problems. I've replaced "Name" with "FirstName".

1) Create a query qryTowns that returns a sorted list of towns (SELECT
DISTINCT Town FROM MyTable ORDER BY Town.

2) Create a second select query qryExport. Your code will modify this,
so it doesn't matter what it does.

3) Build a VBA procedure like this:

Dim dbD as DAO.Database
Dim rsTowns AS DAO.Recordset
Dim strSQL AS String
Dim FileSpec As String

FileSpec = "D:\Folder\MyFile.xls"

Set dbD = CurrentDB()
Set rsTowns = dbD.OpenRecordset("qryTowns")

Do Until rsTowns.EOF
'Build SQL statement to return names for current town
strSQL = "SELECT FirstName FROM MyTable WHERE Town ='" _
& rsTowns.Fields("Town").Value _
& "' ORDER BY FirstName;"

'Assign to query
dbD.QueryDefs("qryExport").SQL = strSQL

'Export, passing the sheet name in the Range argument
docmd.TransferSpreadsheet acExport, , "qryExport", _
FileSpec, True, rsTowns.Fields("Town").Value
Loop

Thanks for replying. Are saying that I have to export seperate queries? One
query for each town?I am very familier with vba coding.

[quoted text clipped - 14 lines]

Please respond in the newgroup and not by email.

 

[ielmrani via AccessMonster.com]

Worked. The mistake I made was that I did not change the name of the file
location.

John, thanks sooooo much for your help. I really appreciate your patience.

I also would like to share another way of doing this using a Form. It's
pretty cool and simple. Here it is:

Created a new Form called frmTown
Set it's RecoredSource to:
SELECT [Town] FROM YourTable GROUP BY [Town];
Add the field [Town] to the Detail section.
Add a Command Button (Command1) to the form with the following code:

CODE
Private Sub Command1_Click()
Me.Recordset.MoveFirst
Do
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryTown", "C:\
Town.xls", True, Me.Town
Me.Recordset.MoveNext
Loop Until Me.Recordset.EOF
End Sub
Then created a new Query (qryTown) with the following SQL:
SELECT YourTable.*
FROM YourTable
WHERE (((YourTable.Town)=[Forms]![frmTown]![Town]));
When I open the form and click the button I get a new Excel workbook (Town.
xls) with a new tab for each town.

Ismail

Hi Ismail,

The only way I've been actually able to generate this error is by
passing a filespec containing invalid characters, though the message
implies that it might also come from a problem with folder permissions
or network connections.

So the first thing to do is to check the value of FileSpec and ensure
that it points to a folder where you have full
(read/write/execute/delete) access and specifies a valid filename.

Hi John,
I did everything you said. However, I am getting the following message:

[quoted text clipped - 58 lines]