[AccessD] Using the SUBST command

Thanks Lambert, you were correct.  It was indeed a problem with the path.

I've never seen the COMSPEC option for the Environ command before.  Can 
you give me the name of a good resource for learning what other options 
are available with Environ?

Cheers
*/Bryan

/*
On 24/07/2013 6:52 AM, Bryan Fitzpatrick wrote:
> 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/6515 - Release Date: 07/23/13