Excel formula not working in Access vba

mikenelena

Board Regular
Joined
Mar 5, 2018
Messages
68
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
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

Watch MrExcel Video

Forum statistics

Threads
1,123,253
Messages
5,600,544
Members
414,387
Latest member
Vincent88

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