Excel formula not working in Access vba

mikenelena

Board Regular
Joined
Mar 5, 2018
Messages
139
Office Version
  1. 365
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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)))"
 
Upvote 0
Solution

Forum statistics

Threads
1,217,753
Messages
6,138,417
Members
450,136
Latest member
Tabako1960

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
Back
Top