# Solved: Run-Time Error 91 Issue



## dpark82 (Aug 13, 2011)

I don't even know where to begin, this has been a very frustrating problem. We are using Windows XP, Visual Studio.net 2008, and Access 2007 and this issue crops up when Access VBA 2007 tries to call a sub-routine in a VB.net object. The error is _Run-Time Error 91: Object variable or With block Variable not set_. This error is caught by Access VBA 2007.

This is the VBA code. I've obviously modified things to keep private information private. Also, this is not for a P2P or file-sharing program, it's actually something I'm stumped on at work. It's just a program that uploads a batch of local files to the company FTP after the user has generated a batch of information. I'm not really allowed to go into more depth than that about the program's purpose.


```
Sub UploadFiles(strFiles() As Variant, strOriginPath As String)
    Const USERNAME As String = "fakeusername"
    Const PASSWORD As String = "fakepassword"
    Const SERVER As String = "www.fakeFTP.com"
    Const DESTINATION As String = "/Local Directory/"
    Dim blnFound As Boolean
    Dim ftpFile As VBFTPProgram.Connect
    Dim L As Long, M As Long
    Dim strDeadFiles() As Variant
     
    'Step 2: Kill the files already in the server folder.
    ReDim strDeadFiles(1)
    blnFound = ftpFile.FTP("LIST FILE", strDeadFiles(), SERVER, USERNAME, PASSWORD, DESTINATION)
    For L = 0 To UBound(strDeadFiles()) - 1
        strDeadFiles(0) = strDeadFiles(L)
        blnFound = ftpFile.FTP("DELETE", strDeadFiles(0), SERVER, USERNAME, PASSWORD, DESTINATION)
    Next L
    
    'Step 3: Upload the files from the local machine to the server.
    For L = 0 To UBound(strFiles()) - 1
        strFiles(0) = strFiles(L)
        blnFound = ftpFile.UploadFile("UPLOAD", strFiles(), SERVER, USERNAME, PASSWORD, DESTINATION, strOriginPath)
    Next L
 End Sub
```
The error occurs on: blnFound = ftpFile.FTP("LIST FILE", strDeadFiles(), SERVER, USERNAME, PASSWORD, DESTINATION)

The routine being called is one that has two versions/is overloaded, with very different numbers of variables required. We've determined, by renaming the VB.net routine something else and changing the appropriate calls in VBA, that it is not the fact the routine is overloaded that causes the problem. I don't /think/ it's the lack of values in the array, because the problem would crop up in a perfectly functional routine elsewhere in the company code-base.

Now for the versions of the FTP function... this switches over to VB.net. I'm reluctant to post the whole thing unless I really have to, so here are the headers...

Version 1 (The one that works but is way too specific):

```
Public Sub FTP(ByRef FileList As Object(,), Optional ByVal strFilePath As String = "ftp://username:[email protected]/", Optional ByVal strUserName As String = "username", Optional ByVal strPassword As String = "password", Optional ByVal DataType As String = ".pdf")
```
Version 2:

```
Public Sub FTP(ByVal strCommand As String, ByRef strFiles As Object(), ByVal strServer As String, ByVal strUsername As String, ByVal strPassword As String, ByVal strFTPDirectory As String, Optional ByVal strLocalDirectory As String = "/")
```
I'm hoping it is something really simple that I'm just overlooking due to my inexperience using VB.net with VBA. What am I missing and how do I fix it?


----------



## Ziggy1 (Jun 18, 2002)

have you checked all the spelling of the variables and made sure they are all declared?


----------



## dpark82 (Aug 13, 2011)

I double-checked to be sure, but there aren't any undeclared variables or misspelled variable names. We set Option Explicit as standard operating procedure in VBA to prevent those sorts of issues and VB.net's compiler will flag undeclared variables. We know that the first version of the FTP routine works, because we're able to download our files from the server using it. But because it's so specific to that particular system, a generalized version had to be written. This is the generalized version...


```
Public Sub FTP(ByVal strCommand As String, ByRef strFiles As Object(), ByVal strServer As String, ByVal strUsername As String, ByVal strPassword As String, ByVal strFTPDirectory As String, Optional ByVal strLocalDirectory As String = "/")
        Dim FTPServer As New FTP(strServer, strUsername, strPassword)
        Dim strFileList As System.Collections.IList
        Dim fleUpload As IO.FileInfo
        Dim L As Long, M As Long
        'Generalized handler for FTP code.
        'strCommand can be LIST, LIST FILE, MAKE DIR, DELETE, UPLOAD, DOWNLOAD
        FTPServer.CurrentDirectory = strFTPDirectory

        If UCase(strCommand) Like "LIST*" Then
            If UCase(strCommand) = "LIST" Then
                'LIST: Returns a list of all the contents of directory specified in strFTPDirectory. Requires empty strFiles(), returns the list in strFiles()
                'Uses: strCommand = "LIST", strFiles() = empty, strServer, strUsername, strPassword, strFTPDirectory
                strFileList = FTPServer.GetFileList("", "*")
                ReDim strFiles(strFileList.Count)
            End If
            If UCase(strCommand) = "LIST DIR" Then
                'LIST DIR: Returns a list of all the sub-directories specified in strFTPDirectory.  Requires empty strFiles(), returns the list of sub-directories in strFiles().
                'Uses: strCommand = "LIST DIR", strFiles()= empty, strServer, strUsername, strPassword, strFTPDirectory
                strFileList = FTPServer.GetFileList("", "*")
                M = 1
                For L = 0 To strFileList.Count - 1
                    'This weeds out all actual files... in theory.
                    If Not (strFileList(CInt(L)).ToString Like "*.*") Then
                        ReDim Preserve strFiles(CInt(M))
                        strFiles(CInt(M - 1)) = strFileList(CInt(L))
                    End If
                Next L
            End If
            If UCase(strCommand) = "LIST FILE" Then
                'LIST FILE: Returns a list of all the files (not directories) specified in strFTPDirectory. Requires empty strFiles(), returns the list of files in strFiles().
                'Uses: strCommand, strFiles()=empty, strServer, strUsername, strPassword, strFTPDirectory
                strFileList = FTPServer.GetFileList("", "*.*")
            End If
            If Not (UCase(strCommand) = "LIST DIR") Then
                ReDim strFiles(strFileList.Count)
                For I = 0 To strFileList.Count - 1
                    strFiles(I) = strFileList(I).ToString
                Next
            End If
        End If
        'MAKE DIR: Creates a new directory in strFTPDirectory.  The first value of strFiles is used to specify the name of the new directory.
        'Uses: strCommand = "MAKE DIR", strFiles(0)=New Directory Name, strServer, strUsername, strPassword, strFTPDirectory, strLocalDirectory
        If UCase(strCommand) = "MAKE DIR" Then
            'Requires a make dir command.
        End If
        'DELETE: Deletes file/directory specified in strFiles(0) contained in strFTPDirectory.
        'Uses: strCommand = "DELETE", strFiles(0) = File to be Deleted, strServer, strUsername, strPassword, strFTPDirectory
        If UCase(strCommand) = "DELETE" Then
            FTPServer.DeleteFile(strFiles(0).ToString)
        End If
        'UPLOAD: Uploads file specified in strFiles(0) from strLocalDirectory to strFTPDirectory.
        'Uses: strCommand = "UPLOAD", strFiles(0) = File to upload, strServer, strUsername, strPassword, strFTPDirectory, strLocalDirectory
        If UCase(strCommand) = "UPLOAD" Then
            fleUpload = New IO.FileInfo(strLocalDirectory & strFiles(0).ToString)
            FTPServer.UploadFile(fleUpload)
        End If
        'DOWNLOAD: Downloads file specified in strFiles(0) from strFTPDirectory to strLocalDirectory.
        'Uses: strCommand = "DOWNLOAD", strFiles(0) = File to Download, strServer, strUsername, strPassword, strFTPDirectory, strLocalDirectory
        If UCase(strCommand) = "DOWNLOAD" Then
            FTPServer.GetFile(strFiles(0).ToString, strLocalDirectory)
        End If
    End Sub
```


----------



## dpark82 (Aug 13, 2011)

Is that a mis-post, hohoo0? It certainly doesn't look like anything I'm working on.


----------



## dpark82 (Aug 13, 2011)

I figured out the unassigned object issue. VBA requires you to assign VB.net objects at run time, you cannot declare them as a VB.net object type or you get an unset object error. Such that:


```
Dim objFTP as object
Set objFTP = Application.COMAddIns("VBFTPProgram.Connect").Object
```
This obviously goes before any calls are made to the object in question.


----------

