CODEWITHSUNDEEP
excelvbams-accessbatch-filesharepoint
Yusuf N.
Yusuf N.

Reputation: 27

Return Drive name to VBA in Access from batch file

I have a Batch file that maps the next available free drive letter to a network drive. I use this to map a sharepoint site and then use the documents in the shared folder in my Access database.

this is the batch file (which works perfectly to map the netwrok)

@echo off
for %%a in (C D E F G H I J K L M N O P Q R S T U V W X Y Z) do CD %%a: 1>> nul 2>&1 & if errorlevel 1 set freedrive=%%a:
echo %freedrive%
pause
Net Use %freedrive% "http://XYZ/" 
pause

I call this batch file as a shell from a form in Access VBA. So after the drive is mapped, I want to know what was the letter assigned so that I can run my MACRO which will link the tables from the databases on this newly mapped (whose letter I don't know) drive to the current operating Access file. I don't know how to return variables from a Batch file to a vba code and then use it for my other work.

EXTRA: I would also like to delete the mapped drive at the end when the user has finished querying or editing the tables. This would generally happen when the user decides to exit Access. I could write another batch file for this but I need to know which drive letter was used in order to delete it!

All help is highly appreciated!

EDIT: I completely traded off the batch file with API calls for which the link is provided in the selected answer. The whole thing was integrated right in access and worked like a charm! and the batch file code is not mine. I got it from here

Upvotes: 0

Views: 998

Answers (2)

Alex K.
Alex K.

Reputation: 175956

Ask the system for a list of current drives. iterate letters until one does not appear in the list.

Pass that as an argument to the batch file.

Alternatively you can also replace the net use with calls to the API; http://support.microsoft.com/kb/173011

Private Declare Function GetLogicalDriveStrings Lib "kernel32" Alias "GetLogicalDriveStringsA" (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Function GetNextFreeDriveLetter() As String
    Dim buff As String, temp As Long
    buff = Space$(255)
    temp = GetLogicalDriveStrings(255, buff)
    If temp > 0 Then
        buff = vbNullChar & Left$(buff, temp)
        For temp = 67 To 90 'ascii C-Z
            If InStr(buff, vbNullChar & Chr$(temp) & ":\") = 0 Then
                GetNextFreeDriveLetter = Chr$(temp) & ":"
                Exit Function
            End If
        Next
    End If
End Function

Upvotes: 0

Horaciux
Horaciux

Reputation: 6487

Environ() gets you the value of any environment variable. These can be found by doing the following command in the Command Prompt or Batch:

SET [variable=[string]]

If you wanted to get the value in variable, you would do:

Environ("variable")

In your particular case, from VBA:

MsgBox Environ("freedrive")

Upvotes: 1

Related Questions