Export data to multiple excel spreadsheets

[Guest]

I found the following code in a previous post (dated 1/9/07):

Function basExport_Bills_Excel()

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 ("qryBills")
On Error GoTo 0
Set qdf = dbs.CreateQueryDef("qryBills")

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

With rst
Do While Not .EOF
strSQL = "SELECT * FROM [tblMonthly_Bills] WHERE [County] = """
& _
![County] & """"
qdf.SQL = strSQL
strPath = " U:\MSHO-DATABASE\VendorBills\" & _
![County] & ".xls"
On Error Resume Next
Kill strPath
On Error GoTo 0
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
TableName:="qryBills", _
FileName:=strPath
.MoveNext
Loop
.Close
End With

Set rst = Nothing
Set dbs = Nothing

End Function

When I run the code, I get the following error:
"Run-time error '3044':
'U:\MSHO-DATABASE\VendorBills\Aitkin.xls' is not a valid path. Make sure
that the path name is spelled correctly and that you are connected to the
server on which the file resides."

When I debug, the following is highlighted as the issue:
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
TableName:="qryBills", _
FileName:=strPath

Will someone PLEASE offer some assistance?

THANKS!

 

[SusanV]

Is the U: drive mapped properly? Personally, I avoid mapped drives like the
plague, as users may disconnect them or change them - try the UNC:

"\\ServerName\ShareName\Subfolder\filename.xls"

 

[Guest]

Tried it and am still getting the same error...

Is the U: drive mapped properly? Personally, I avoid mapped drives like the
plague, as users may disconnect them or change them - try the UNC:

"\\ServerName\ShareName\Subfolder\filename.xls"
--
hth,
SusanV

I found the following code in a previous post (dated 1/9/07):

Function basExport_Bills_Excel()

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 ("qryBills")
On Error GoTo 0
Set qdf = dbs.CreateQueryDef("qryBills")

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

With rst
Do While Not .EOF
strSQL = "SELECT * FROM [tblMonthly_Bills] WHERE [County] = """
& _
![County] & """"
qdf.SQL = strSQL
strPath = " U:\MSHO-DATABASE\VendorBills\" & _
![County] & ".xls"
On Error Resume Next
Kill strPath
On Error GoTo 0
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
TableName:="qryBills", _
FileName:=strPath
.MoveNext
Loop
.Close
End With

Set rst = Nothing
Set dbs = Nothing

End Function

When I run the code, I get the following error:
"Run-time error '3044':
'U:\MSHO-DATABASE\VendorBills\Aitkin.xls' is not a valid path. Make sure
that the path name is spelled correctly and that you are connected to the
server on which the file resides."

When I debug, the following is highlighted as the issue:
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
TableName:="qryBills", _
FileName:=strPath

Will someone PLEASE offer some assistance?

THANKS!