[AccessD] Using the SUBST command

Thanks Lambert

I will give that a try when I get to work.

Cheers
*/Bryan

/*
On 24/07/2013 1:21 AM, Heenan, Lambert wrote:
> 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




-----
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