Reading active path of access application to open an Excel F Version:

[Worsty]

We are about to do a demo of an application and we just needed a way that when we copy a db onto a CD that it looks in the ActiveFolder Path of the Access Database for an Excel File. When we specify the full length of the file in the str Argument it dinds it just fine.

How can we change str in the code to read the CurrentDB folder path plus the document named Quote_Template_File.xls so it runs like it odes in Production.

Someone suggested changing the string to:

str = CurrentProject.Path & "Quote_Template_File.xls"

but I get the following error:

External table is not in the expected format.


Thanks
*********************************************************

On Error GoTo EXPORT_TEST_Err
Dim str As String


str = "X:\Test\SDS_DB\" & "Quote_Template_File.xls"

If FileAlreadyOpen(str) Then
MsgBox " The Short Quote is Open Please close and try again", vbOKOnly, "Error!"
Exit Sub
End If
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("A_SUBMISSION")
qdf.SQL = "SELECT * FROM A_SUBMISSION_STRING_FINAL " _
& "WHERE Submission_ID= Forms!Frm_Submissions!Text193"

Set qdf = Nothing
db.Close
Set db = Nothing
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me!Combo9.Requery
Me!Combo9 = Me!Text193
DoCmd.TransferSpreadsheet acExport, 8, "A_SUBMISSION", str, True, ""

FollowHyperlink str
EXPORT_TEST_Exit:
Exit Sub

EXPORT_TEST_Err:
MsgBox Error$
Resume EXPORT_TEST_Exit

 

[PHV]

str = CurrentProject.Path & "[!]\[/!]Quote_Template_File.xls"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Worsty]

Thanks so much! That did it.....