Solved Resolving Error 91 (1 Viewer)

Samantha

still learning...
Local time
Today, 05:32
Joined
Jul 12, 2012
Messages
180
Hello,

I am looking for help in resolving error 91. In my DB I am creating an Excel file generated from a template then I am saving it to the network. I am getting the error on this line ActiveWorkbook.SaveAs strFileNamePath and have found the cause to be created by having the preview pane open in the folder location I am trying to manipulate. I'd like to close the preview pane before generating the template? However I have come to a dead end on guidance for manipulating it. Anyone have an idea?

Code:
'check if file exists and save to network location
    If Dir(strFileNamePath, vbDirectory) = "" Then
    ActiveWorkbook.SaveAs strFileNamePath
    MsgBox "Your project sheet is ready and has been saved.", vbOKOnly, "Successful"
    Else
    MsgBox "Your project sheet is ready but this may be a duplicate. You must manually save this file.", vbExclamation
    End If

Thanks in Advance!
Samantha
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:32
Joined
May 7, 2009
Messages
19,175
access vba?

there is no ActiveWorkbook object in access.
somewhere in front of your code you create a Workbook object, then save it:

dim oXL as Object
dim oWB as Object
set oXL = CreateObject("Excel.Application")
set oWB = oXL.Workbooks.Open("theworkbook.xlsx")
..
..
oWB.SaveAs strFileNamePath
 

Samantha

still learning...
Local time
Today, 05:32
Joined
Jul 12, 2012
Messages
180
access vba?

there is no ActiveWorkbook object in access.
somewhere in front of your code you create a Workbook object, then save it:

dim oXL as Object
dim oWB as Object
set oXL = CreateObject("Excel.Application")
set oWB = oXL.Workbooks.Open("theworkbook.xlsx")
..
..
oWB.SaveAs strFileNamePath
Hi Arnel,

Yes, I am creating the object and saving it to the file location however when the file folder is open and the preview pane is activated it is failing to save and giving me the error 91. I am looking for a way to use vba and either make the network file close completely before generating or to close the preview pane.
Here is all of the code if it makes more sense. Thanks for looking.

Code:
Private Sub cmdMergeXLbttn_Click()
'On Error GoTo Error_Handler

'Declare a variable named MySheetPath as string
Dim MySheetPath As String
Dim strFileNamePath As String
Dim strLink As String

'set file path to actual sheet
MySheetPath = "O:\Access\ProjectSheet.xlsx"

'Set up object variables to refer to Excel and Objects
Dim XL As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim strServiceAddress As String
Dim ctl As Control
Dim varItem As Variant
Dim BillingAddress As String
Dim AddyLineVar As String
Dim CompanyAddress As String
Dim SalutationVar As String
Dim CustCompany As String
Dim CustEmail As String
Dim EmailCC As String
Dim CustCell As String

'Open an instance of Excel, open the workbook.
Set XL = CreateObject("Excel.Application")
Set XLBook = XL.Workbooks.Open(MySheetPath)
Set XLSheet = XLBook.Worksheets(1)

'Make sure everything is visible
XL.Visible = True
XLBook.Application.Workbooks(1).Activate
XLBook.Windows(1).Visible = True

'Define top sheet in Workbook as XLSheet
Set XLSheet = XLBook.Worksheets(1)

'Set Company Address
CompanyAddress = [Company] & vbCrLf & ([sfrmContacts].[Form]![MailingAddress]) & vbCrLf & ([sfrmContacts].[Form]![City]) & ", " & ([sfrmContacts].[Form]![State]) & " " & ([sfrmContacts].[Form]![ZipCode])

'Start building AddyLineVar, by dealing with blank last name fields.
    If IsNull([sfrmContacts].[Form]![Last]) Then
        AddyLineVar = [Company]
        'Just set salutation to generic.
        SalutationVar = "Sir or Madam"
    Else
        AddyLineVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last])
        
        'Add Company on after name.
        If Not IsNull([Company]) Then
            AddyLineVar = AddyLineVar & vbCrLf & [Company]
        End If
        
        'Salutation will be customer's last name
        SalutationVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![Last]) & ", "
    End If
        
    'Add line break and Address lines.
    AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![MailingAddress])
    'Tack on line break then city, state, and zip.
    AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![City]) & ", "
    AddyLineVar = AddyLineVar & ([sfrmContacts].[Form]![State]) & " " & ([sfrmContacts].[Form]![ZipCode])
    CustCompany = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last]) & vbCrLf & [Company]
    
'Start building BillingAddress
    If IsNull([Billing Information].Form![BillToCompany]) Then
       BillingAddress = AddyLineVar
    Else
       BillingAddress = ([Billing Information].Form![BillToCompany])
      
       If Not IsNull([Billing Information].Form![BillCareOf]) Then
            BillingAddress = BillingAddress & vbCrLf & "c/o " & ([Billing Information].Form![BillCareOf])
       End If
      
       If Not IsNull([Billing Information].Form![BillBoxNumber]) Then
            BillingAddress = BillingAddress & vbCrLf & ([Billing Information].Form![BillBoxNumber])
       End If
      
       If Not IsNull([Billing Information].Form![BillAttn]) Then
            BillingAddress = BillingAddress & vbCrLf & "Attn: " & ([Billing Information].Form![BillAttn])
       End If
            
       'Add line break and Address lines.
       BillingAddress = BillingAddress & vbCrLf & ([Billing Information].Form![BillAddress])
       'Tack on line break then city, state, and zip.
       BillingAddress = BillingAddress & vbCrLf & ([Billing Information].Form![BillCity]) & ", "
       BillingAddress = BillingAddress & ([Billing Information].Form![State]) & " " & ([Billing Information].Form![BillingZip])
    End If

'Build CustEmail
        If IsNull([Billing Information].Form![BillEmailTo]) Then
            CustEmail = Nz([sfrmContacts].[Form]![E-mail address], "None Listed")
        Else: CustEmail = ([Billing Information].Form![BillEmailTo])
        End If

'Build EmailCC
        If IsNull([Billing Information].Form![BillEmailToCC]) Then
            EmailCC = "None Requested"
        Else: EmailCC = ([Billing Information].Form![BillEmailToCC])
        End If
        
' String Customer Cell Phone
If Not IsNull([sfrmCellPhone].Form![Mobile Phone]) Then
    CustCell = [sfrmCellPhone].Form![Mobile Phone]
Else: CustCell = ""
End If

'Make Tax_Exempt field useful information
If TaxExempt = -1 Then
    TaxExempttxt = "Yes"
Else: TaxExempttxt = ""
End If

XLSheet.Range("DateGen") = [sfrmJobInformation].[Form]![DateAdded]
XLSheet.Range("ProjectNumber") = "'" & JobNumber & ([sfrmPMTitles].[Form]![JobExtension])
XLSheet.Range("CompanyAdd") = CompanyAddress
XLSheet.Range("Contact") = ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last])
XLSheet.Range("Phone") = ([sfrmContacts].[Form]![Phone])
XLSheet.Range("Fax") = ([sfrmContacts].[Form]![BusinessFax])
XLSheet.Range("ProjectDescription") = ProjectDescription
XLSheet.Range("ServiceAdd") = Me!ServiceAddress.Value 'returned only first selection when combo box is used, text box transfers whole string
XLSheet.Range("City") = City
XLSheet.Range("State") = State
XLSheet.Range("ProjectType") = ([sfrmProjectType].[Form]![ProjectType])
XLSheet.Range("PurchaseOrder") = PONumber
XLSheet.Range("OnsiteContact") = [OnsiteContact]
XLSheet.Range("BillTo") = BillingAddress
XLSheet.Range("CellPhone") = CustCell
XLSheet.Range("CustEmail") = CustEmail
XLSheet.Range("EmailCC") = EmailCC
XLSheet.Range("PM") = [Manager]
XLSheet.Range("ExemptStatus") = TaxExempttxt
'XLSheet.Range("Hazards") = AHA

'With Range("Hazards")
'.Font.Italic = True
'.Value = "Hazards" & "Controls"
'End With

    'set file path
    strLink = Replace(Link, "#", "")
    strFileNamePath = (strLink & "\" & [JobNumber] & " " & "ProjectSheet" & ".xlsx")
    
    'check if file exists and save to network location
    If Dir(strFileNamePath, vbDirectory) = "" Then
    ActiveWorkbook.SaveAs strFileNamePath
    MsgBox "Your project sheet is ready and has been saved.", vbOKOnly, "Successful"
    Else
    MsgBox "Your project sheet is ready but this may be a duplicate. You must manually save this file.", vbExclamation
    End If

Exit_Procedure:
Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application." & " Please contact your technical support with the following information:" & vbCrLf & vbCrLf & "Error Number" & " " & Err.Number & ", " & Err.Description, Buttons:=vbCritical
    Resume Exit_Procedure
    
End Sub
 

Samantha

still learning...
Local time
Today, 05:32
Joined
Jul 12, 2012
Messages
180
So either do not set Xl.Visible to true, or set it to false before trying to save?

However I do not think that would stop the SaveAs ?
Well, in either condition if I comment that line out or set it to False it saves it to the file in a locked condition that I can only open in read only. Additionally it will not let me delete the file from the network at all.

I will have to look more into your other suggestions next week. I am just kinda looking at it symptomatically I didn't see anything that stood out to me. However - I am very much the amateur. The only changes I have made recently are saving in the file location and the only time I get an error is when the preview pane is open/active.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:32
Joined
Sep 21, 2011
Messages
14,050
Well you need to close the file?
I'd also set all the xl objects to Nothing.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:32
Joined
Feb 28, 2001
Messages
27,001
Syntactically, there are only two places in your statement that can go wrong.

ActiveWorkbook.SaveAs strFileNamePath

If you put a breakpoint in the code at this statement you can hover your mouse over each one to see what it contains. The strFileNamePath should contain a string. The ActiveWorkbook should be an object of some sort. If either of those is not true, you have identified the problem. Then all you have to do is work backwards to find out WHY that is the problem (whatever you found...)
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:32
Joined
Sep 21, 2011
Messages
14,050
@Doc_Man
But as @arnelgp pointed out, there is no ActiveWorkbook in Access?

Wouldn't you need Xl.ActveWorkbook ?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:32
Joined
May 7, 2009
Messages
19,175
after using excel objects, close it.
see changes to your code:
Code:
Private Sub cmdMergeXLbttn_Click()
'On Error GoTo Error_Handler

'Declare a variable named MySheetPath as string
Dim MySheetPath As String
Dim strFileNamePath As String
Dim strLink As String

'set file path to actual sheet
MySheetPath = "O:\Access\ProjectSheet.xlsx"

'Set up object variables to refer to Excel and Objects
Dim XL As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim strServiceAddress As String
Dim ctl As Control
Dim varItem As Variant
Dim BillingAddress As String
Dim AddyLineVar As String
Dim CompanyAddress As String
Dim SalutationVar As String
Dim CustCompany As String
Dim CustEmail As String
Dim EmailCC As String
Dim CustCell As String

'Open an instance of Excel, open the workbook.
Set XL = CreateObject("Excel.Application")
Set XLBook = XL.Workbooks.Open(MySheetPath)
Set XLSheet = XLBook.Worksheets(1)

'Make sure everything is visible
XL.Visible = True
XLBook.Application.Workbooks(1).Activate
XLBook.Windows(1).Visible = True

'Define top sheet in Workbook as XLSheet
Set XLSheet = XLBook.Worksheets(1)

'Set Company Address
CompanyAddress = [Company] & vbCrLf & ([sfrmContacts].[Form]![MailingAddress]) & vbCrLf & ([sfrmContacts].[Form]![City]) & ", " & ([sfrmContacts].[Form]![State]) & " " & ([sfrmContacts].[Form]![ZipCode])

'Start building AddyLineVar, by dealing with blank last name fields.
    If IsNull([sfrmContacts].[Form]![Last]) Then
        AddyLineVar = [Company]
        'Just set salutation to generic.
        SalutationVar = "Sir or Madam"
    Else
        AddyLineVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last])
        
        'Add Company on after name.
        If Not IsNull([Company]) Then
            AddyLineVar = AddyLineVar & vbCrLf & [Company]
        End If
        
        'Salutation will be customer's last name
        SalutationVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![Last]) & ", "
    End If
        
    'Add line break and Address lines.
    AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![MailingAddress])
    'Tack on line break then city, state, and zip.
    AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![City]) & ", "
    AddyLineVar = AddyLineVar & ([sfrmContacts].[Form]![State]) & " " & ([sfrmContacts].[Form]![ZipCode])
    CustCompany = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last]) & vbCrLf & [Company]
    
'Start building BillingAddress
    If IsNull([Billing Information].Form![BillToCompany]) Then
       BillingAddress = AddyLineVar
    Else
       BillingAddress = ([Billing Information].Form![BillToCompany])
      
       If Not IsNull([Billing Information].Form![BillCareOf]) Then
            BillingAddress = BillingAddress & vbCrLf & "c/o " & ([Billing Information].Form![BillCareOf])
       End If
      
       If Not IsNull([Billing Information].Form![BillBoxNumber]) Then
            BillingAddress = BillingAddress & vbCrLf & ([Billing Information].Form![BillBoxNumber])
       End If
      
       If Not IsNull([Billing Information].Form![BillAttn]) Then
            BillingAddress = BillingAddress & vbCrLf & "Attn: " & ([Billing Information].Form![BillAttn])
       End If
            
       'Add line break and Address lines.
       BillingAddress = BillingAddress & vbCrLf & ([Billing Information].Form![BillAddress])
       'Tack on line break then city, state, and zip.
       BillingAddress = BillingAddress & vbCrLf & ([Billing Information].Form![BillCity]) & ", "
       BillingAddress = BillingAddress & ([Billing Information].Form![State]) & " " & ([Billing Information].Form![BillingZip])
    End If

'Build CustEmail
        If IsNull([Billing Information].Form![BillEmailTo]) Then
            CustEmail = Nz([sfrmContacts].[Form]![E-mail address], "None Listed")
        Else: CustEmail = ([Billing Information].Form![BillEmailTo])
        End If

'Build EmailCC
        If IsNull([Billing Information].Form![BillEmailToCC]) Then
            EmailCC = "None Requested"
        Else: EmailCC = ([Billing Information].Form![BillEmailToCC])
        End If
        
' String Customer Cell Phone
If Not IsNull([sfrmCellPhone].Form![Mobile Phone]) Then
    CustCell = [sfrmCellPhone].Form![Mobile Phone]
Else: CustCell = ""
End If

'Make Tax_Exempt field useful information
If TaxExempt = -1 Then
    TaxExempttxt = "Yes"
Else: TaxExempttxt = ""
End If

XLSheet.Range("DateGen") = [sfrmJobInformation].[Form]![DateAdded]
XLSheet.Range("ProjectNumber") = "'" & JobNumber & ([sfrmPMTitles].[Form]![JobExtension])
XLSheet.Range("CompanyAdd") = CompanyAddress
XLSheet.Range("Contact") = ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last])
XLSheet.Range("Phone") = ([sfrmContacts].[Form]![Phone])
XLSheet.Range("Fax") = ([sfrmContacts].[Form]![BusinessFax])
XLSheet.Range("ProjectDescription") = ProjectDescription
XLSheet.Range("ServiceAdd") = Me!ServiceAddress.Value 'returned only first selection when combo box is used, text box transfers whole string
XLSheet.Range("City") = City
XLSheet.Range("State") = State
XLSheet.Range("ProjectType") = ([sfrmProjectType].[Form]![ProjectType])
XLSheet.Range("PurchaseOrder") = PONumber
XLSheet.Range("OnsiteContact") = [OnsiteContact]
XLSheet.Range("BillTo") = BillingAddress
XLSheet.Range("CellPhone") = CustCell
XLSheet.Range("CustEmail") = CustEmail
XLSheet.Range("EmailCC") = EmailCC
XLSheet.Range("PM") = [Manager]
XLSheet.Range("ExemptStatus") = TaxExempttxt
'XLSheet.Range("Hazards") = AHA

'With Range("Hazards")
'.Font.Italic = True
'.Value = "Hazards" & "Controls"
'End With

    'set file path
    strLink = Replace(Link, "#", "")
    strFileNamePath = (strLink & "\" & [JobNumber] & " " & "ProjectSheet" & ".xlsx")
    
    'check if file exists and save to network location
    If Dir(strFileNamePath, vbDirectory) = "" Then
    'arnelgp
    'ActiveWorkbook.SaveAs strFileNamePath
    XLBook.SaveAs strFileNamePath
    MsgBox "Your project sheet is ready and has been saved.", vbOKOnly, "Successful"
    Else
    MsgBox "Your project sheet is ready but this may be a duplicate. You must manually save this file.", vbExclamation
    End If

Exit_Procedure:
'arnelgp
'housekeeping
'clear oll objects
If Not (XLSheet Is Nothing) Then
    Set XLSheet = Nothing
End If
If Not (XLBook Is Nothing) Then
    XLBook.Close
    Set XLBook = Nothing
End If
If Not (XL Is Nothing) Then
    XL.Quit
    Set XL = Nothing
End If
Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application." & " Please contact your technical support with the following information:" & vbCrLf & vbCrLf & "Error Number" & " " & Err.Number & ", " & Err.Description, Buttons:=vbCritical
    Resume Exit_Procedure
    
End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:32
Joined
Feb 28, 2001
Messages
27,001
True enough, except that if you have the Excel Library in your references, you DO have ActiveWorkbook as a shortcut. Having an unqualified reference to it would still make it "NOTHING" (as an object) but could account for the error 91 (Object not defined). Note that the error wasn't "Undefined variable" (which would be a compiler error), it was "Object not defined" which is a Run-Time error. Thus the code compiled without error. You just can't run it.
 

Samantha

still learning...
Local time
Today, 05:32
Joined
Jul 12, 2012
Messages
180
Syntactically, there are only two places in your statement that can go wrong.

ActiveWorkbook.SaveAs strFileNamePath

If you put a breakpoint in the code at this statement you can hover your mouse over each one to see what it contains. The strFileNamePath should contain a string. The ActiveWorkbook should be an object of some sort. If either of those is not true, you have identified the problem. Then all you have to do is work backwards to find out WHY that is the problem (whatever you found...)
Sorry for the delay in getting back to this- how I would love it to be my first priority! ActiveWorkbook returns as nothing and I double checked that excel references are selected. I incorporated Arnelgp's suggested code and so far so good. Thank you!
 

Users who are viewing this thread

Top Bottom