[AccessD] Using the SUBST command

Golly. A good resource??? I dredged the info up from memory of my bad of DOS days - pre-Windows. Basically the ENVIRON function in VBA will return the value of any variable in the DOS environment. To see the kind of things out there open a DOS box and run the command SET. It will list everything on the current system's environment.

A few links Google turned up

http://en.wikipedia.org/wiki/Environment_variable
http://support.microsoft.com/kb/121170
http://www.mydigitallife.info/list-of-windows-and-dos-command-prompt-environment-variables/  This looks quite good.

http://www.vijayforvictory.com/how-to/how-to-display-all-the-environment-variables-in-windows-and-unix/3124/



Glad to have helped sort out your problem though.


Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bryan Fitzpatrick
Sent: Wednesday, July 24, 2013 4:58 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com