Enable code to work on either C:\ or Server

[Guest]

The code below works fine on the C:\ drive. When I transfer the database to a
server I have to manually replace C:\ by typing in the full UNC path for
every instance. What additions to the code do I have to make for it to be
able to run in either location?

Code
Dim StrBDM As String
Dim StrPath As String

StrBDM = ("qctb" & Forms![frmReportOps]![cboBDM])
StrPath = "C:\VolumeDatabase\Reports\" & txtFileName &
".xls"
DoCmd.SetWarnings False
Me.Form.Requery
DoCmd.CopyObject "C:\VolumeDatabase\ProcessReport.mdb",
StrBDM, acQuery, "qctbReport"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, StrBDM,
StrPath, True
DoCmd.DeleteObject acQuery, StrBDM
DoCmd.SetWarnings True

 

[Arvin Meyer [MVP]]

Check out "Conditional Compilation" in the VBA Helpfiles

You might also just do your own with something like this:

Const fDebug As Boolean = 0

MySub()
Dim strPath As String
If fDebug = True Then
StrPath = "C:\VolumeDatabase\Reports\" & txtFileName & ".xls"
Else
StrPath = "\\ServerName\ShareName\" & txtFileName & ".xls"
End If
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Douglas J Steele]

What's the relationship between C:\VolumeDatabase\Reports\ and wherever the
database from which you're running the code is located? If that's the same
folder, you can simply detect where the current database is located and use
that, rather than hard coding it.

In Access 2000 and newer, you can use Application.CurrentProject.Path. In
Access 97 and previous, you can use Left(CurrentDb.Name,
Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))