Excel formula not working in Access vba

mikenelena

Board Regular
Joined
Mar 5, 2018
Messages
76
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.
Excel Formula:
=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!

VBA Code:
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
80
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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.
Excel Formula:
=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!

VBA Code:
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)))"
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,009
Messages
5,834,876
Members
430,325
Latest member
Thony

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top