Help with adding text fields from access form to excel

[DarkOne72]

Hello --
How do I add the text field(s) from the access form to the bottom of the list (1st row without data in it) for the 4 columns in the excel file if it isn't present on the lookup? I know it goes under the spot "ELSE ... 'did not find the value " but I don't know what the coding is, please assist.

Private Sub cmdReplace_Number_Click()
Tech_DB_Changed = "Updated!"

    Dim xl As Object, rFound As Object
    Set xl = CreateObject("Excel.Application")
    
    With xl.Workbooks.Open(FileName:= _
    "\\MyShare\App_Data\Tech_Test.xls")
    
        With .Sheets(1)
        'look in sheet 1 column A
            Set rFound = .Columns(1).Find( _
                What:=[Tech_Number], _
                After:=.Range("A1"), _
                LookIn:=xlFormulas, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlNext, _
                MatchCase:=False, _
                SearchFormat:=False)
                
            If Not rFound Is Nothing Then
            'found the value
                .Cells(rFound.Row, "D").Value = [New_Number]
            Else
            'did not find the value
                
             
            End If
        End With
        xl.DisplayAlerts = False
        .Save
        .Close
        xl.DisplayAlerts = True
    End With
    
    Set rFound = Nothing
    Set xl = Nothing
End Sub

Thanks in advance...

 

[SkipVought]

changed all columns to text

Format changes NOTHING!!!

The underlying data remains as it WAS.

Please do this and report back:

1. Find a phone number that appears as (385) 555-9876

2. SELECT that cell and view the value in the FORMULA BAR.

What is the value in the CELL?
What is the value in the FORMULA BAR?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DarkOne72]

Ok,
I went to the excel file where access has added that phone number and in the cell it reads: (385) 555-9876 also in the formula bar it reads: (385) 555-9876 If I change that field to text it still shows (385) 555-9876 in both locations.

 

[SkipVought]

Ok, when that is ACTUALLY TEXT, because the PARENTHESES are characters in the string.

but the other ones that were manually typed in goes got: 3855559876

Now these will appear to be NUMBERS, unless you PREPEND with an APOSTROPHY, as '3855559876.

The essential concept is that ALL data value must be either ALL TEXT or ALL NUMERIC: in your case ALL TEXT, due to the way that Access is sending the data.





Skip,

_{Just traded in my old subtlety...
for a NUANCE!}