Excel formula not working in Access vba

[mikenelena]

I am in the beginning stages of an attempt to capture data from Outlook e-mail messages into an Access database. There's probably a better way, but my first idea is to copy/paste the message body into Excel, then match cells to fields. There is enough consistency in the e-mail formatting to do this. One piece of data I need is a zip code. Unfortunately, it ends up in the same cell as the rest of the address info. I can isolate it to an adjacent cell with this nice formula that I found searching this forum.

=Trim(Right(Substitute(B31, ",", Rept(" ", Len(B31))), Len(B31)))

But, the formula throws an error when I try to use it in the Access vba. I am hoping it's a simple syntax tweak and that someone can help me correct it. Thanks very much!

Private Sub cmdNewFromEmail_Click()

Dim xl As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim filePath As String
Dim strWhere As String

Set xl = New Excel.Application
filePath = "S:\Employee\" & "Excel Test File" & ".xlsx"
   
    Set xlBook = xl.Workbooks.Open(filePath)
   
    Set xlSheet = xlBook.Worksheets(1)
   
    xl.Visible = True


    DoCmd.GoToRecord , , acNewRec
           Me.File_Number = Nz(DMax("File_Number", "ClaimInfo1", strWhere), 0) + 1
          
           Me.Invoice_Number = Me.File_Number & "-01"
          
           DoCmd.RunCommand acCmdSaveRecord
          
Me.SubformContainer.SourceObject = "Appraisals_Subform2"   'Binds Client Billing tab to Invoicing form when setting up new file
Forms!Invoicing_Form.TabCtlEval = 0                        'Sets focus on the appropriate tab.

With xlSheet

.Range("C31").Formula = "=Trim(Right(Substitute(B31, ",", Rept(" ", Len(B31))), Len(B31)))"

End With

Me.Claim_Number = Range("B2")
Me.Vehicle_Owner = Range("B44")
Me.cboAdjusterName = Range("B16")
Me.[Date Of Loss] = Range("B8")

End Sub

 

[JB2020]

I am in the beginning stages of an attempt to capture data from Outlook e-mail messages into an Access database. There's probably a better way, but my first idea is to copy/paste the message body into Excel, then match cells to fields. There is enough consistency in the e-mail formatting to do this. One piece of data I need is a zip code. Unfortunately, it ends up in the same cell as the rest of the address info. I can isolate it to an adjacent cell with this nice formula that I found searching this forum.

=Trim(Right(Substitute(B31, ",", Rept(" ", Len(B31))), Len(B31)))

But, the formula throws an error when I try to use it in the Access vba. I am hoping it's a simple syntax tweak and that someone can help me correct it. Thanks very much!

Private Sub cmdNewFromEmail_Click()

Dim xl As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim filePath As String
Dim strWhere As String

Set xl = New Excel.Application
filePath = "S:\Employee\" & "Excel Test File" & ".xlsx"
  
    Set xlBook = xl.Workbooks.Open(filePath)
  
    Set xlSheet = xlBook.Worksheets(1)
  
    xl.Visible = True


    DoCmd.GoToRecord , , acNewRec
           Me.File_Number = Nz(DMax("File_Number", "ClaimInfo1", strWhere), 0) + 1
         
           Me.Invoice_Number = Me.File_Number & "-01"
         
           DoCmd.RunCommand acCmdSaveRecord
         
Me.SubformContainer.SourceObject = "Appraisals_Subform2"   'Binds Client Billing tab to Invoicing form when setting up new file
Forms!Invoicing_Form.TabCtlEval = 0                        'Sets focus on the appropriate tab.

With xlSheet

.Range("C31").Formula = "=Trim(Right(Substitute(B31, ",", Rept(" ", Len(B31))), Len(B31)))"

End With

Me.Claim_Number = Range("B2")
Me.Vehicle_Owner = Range("B44")
Me.cboAdjusterName = Range("B16")
Me.[Date Of Loss] = Range("B8")

End Sub

Looks like you need to double up on the quotation marks in your string i.e. "=Trim(Right(Substitute(B31, "","", Rept("" "", Len(B31))), Len(B31)))"

 

[mikenelena]

Looks like you need to double up on the quotation marks in your string i.e. "=Trim(Right(Substitute(B31, "","", Rept("" "", Len(B31))), Len(B31)))"

Perfect, thank you!!