[AccessD] Using the SUBST command

Bryan,

This works for me...

Sub subst_Test()
    Const MYPATH = "L:\Applications"
    Const TESTFILE = "MyFile.txt"
    Dim fh As Long
    Dim Com As String

    Com = Environ("COMSPEC") ' get the correct path to the command line processor

    Shell (Com & " /c SUBST X: " & MYPATH) ' subst a drive letter
    ' output some data
    fh = FreeFile
    Open "X:\" & TESTFILE For Output As fh
    Print #fh, Date
    Close #fh
    MsgBox "Check folder" ' pause to take a peek
    Shell (Com & " /c SUBST X: /d") ' drop the subst drive
End Sub

I suspect that your problem may be the hard coded path to CMD.EXE. The above shows one way to get the correct path on any system.

HTH

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bryan Fitzpatrick
Sent: Tuesday, July 23, 2013 9:32 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Using the SUBST command

Hi List

Has anyone used the SUBST command from VBA?

I am currently developing a system in Access 2010 which requires data to be exported to a Word template.  Easily done.
The problem comes in with the length of the paths involved with the storage folder for the templates and the created documents.

I would like to use the SUBST command to map the path to a drive, action the import or export, and then disconnect the drive.
But I cannot get the command to run.

Code snippet:
....
....
....
Save:
             Shell ("c:\cmd.exe /c SUBST X: " & gstrDraftIMPPath)

             myFileName = "ServiceNo " & mRsClients!ServiceID & "- Client " & mRsClients!ClientName _
                                 & " Personal Officer " & mRsClients!PersonalOfficerName

             .SaveAs "X:\" & myFileName, wdFormatDocument
         End With 'my word doc


If I map the drive manually, it all works perfectly.  But I can't get it to map automatically.  Has anyone done this before, or guide me to the correct Shell syntax if it's wrong

Thanks in Advance
--
*/Bryan
/*



-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3349 / Virus Database: 3204/6513 - Release Date: 07/23/13
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com