where to export to?

[Homer]

I am useing 'DoCmd.TransferSpreadsheet' to transfer a table from an Access
data base to a range in an Excel spreadsheet. Because there are many users
and this is a stand alone data base (everyone has multiple copies on thier
laptops) I need a better way of telling Access where the spread sheet is
rather than putting a complete path in the VBA command. Does anyone have any
Ideas?

 

[Jack Leach]

You'll have to somehow refer to the path of the database and have your code
make a decision from there. One way would be to make a function to select
the path you would like based on Application.CurrentProject.Path

For example:

Function fOutputPath() As String
Dim Ret As String
Select Case Application.CurrentProject.Path
Case "C:\ThisPath\"
Ret = "C:\ThisPath\ThisReport.xls"
Case "C:\ThatPath\"
Ret = "C:\ThatPath\ThatReport.xls"
Case Else
Msgbox "Path Not Found!"
End Select
fOutputPath = Ret
End Function

Then in your normal procedure you would use something like

Private Sub btnRunReport_Click()
Dim strOutput As String
DoCmd.TransferSpreadsheet "tablename", fOutputPath(), , etc
End Sub



Another alternative would be to store the desired path in the frontend's
local table and get the value from DLookup. This would work if each output
path is dependant upon which db it's coming from.

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Homer]

Thanks for the reply Jack, I played around with the code you suggested but
never got it to work. I've never used the 'case' so I'm not sure what this
code is trying to do. Is the stetement " Select Case
Application.CurrentProject.Path " retreiving the current path of the
application running? If so what is the next line "Case "C:\ThisPath\""
accomplishing? If you can help I appreciate it and I'll apploigize for being
slow to catch on to this. Thanks again.