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]

hi,

    [highlight]Dim lRow As Long[/highlight]
    
    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
                [highlight]lRow = rFound.Row[/highlight]
            Else
            'did not find the value
                [highlight]lRow = .Cells(1, 1).CurrentRegion.Rows.Count + 1[/highlight]
            End If
                         
            [highlight].Cells(lRow, , "D").Value = [New_Number][/highlight]
        End With
        xl.DisplayAlerts = False
        .Save
        .Close
        xl.DisplayAlerts = True
    End With

Skip,

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

 

[DarkOne72]

Wow Skip, thanks for the fast reply!! I get an error though when adding the above in yellow
It says: Run-Time error '91' Object variable or With block variable not set.

also, to make sure this will still look and find it by the record and if found change column D but if it isnt there it will add it to the list?

 

[DarkOne72]

oh, I almost forgot if it isnt there it will be adding 4 fields a,b,c,d in excel from 4 fields on the access form.

 

[SkipVought]

error on what statement?

Skip,

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

 

[DarkOne72]

Sorry...

.Cells(lRow, , "D").Value = [New_Number]

 

[SkipVought]

Debug and determine what the actial values for, lRow & [New_Number]

faq707-4594



Skip,

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

 

[DarkOne72]

ok, now i am getting error 450
Wrong number of arguments or invalid property assignment

 

[SkipVought]

It help NO ONE to just say, "ok, now i am getting error xyz!!!"

As I previously asked, 1) on what statement and 2) VALUES of variables in that debug!

Skip,

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

 

[DarkOne72]

It says Wrong number of arguments or invalid property assignment
with the highlighted line : .Cells(lRow, , "D").Value = [New_Number]
and lRow=393 , [New_Number = (386) 553-9231

This is all I can get from it..sorry for the inconvience

 

[SkipVought]

Sorry, I had an extra COMMA -- ONE ONLY

Skip,

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

 

[DarkOne72]

This is working fine, but it creates a new problem..I have it going to the excel sheet from the access form but when I use SQL to pull data from the excel file to a website it only shows columns a,b,c but not D which is the phone number..I haven't changed any formatiing or anything but can't seem to get it to work properly...This happens if it is newly inserted or changed.
Any thoughts..?

    Dim xl As Object, rFound As Object
    Set xl = CreateObject("Excel.Application")
    Dim lRow As Long
    
    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
            lRow = rFound.Row
                
            Else
            'did not find the value
            lRow = .Cells(1, 1).CurrentRegion.Rows.Count + 1
             
            End If
            
            .Cells(lRow, "A").Value = [Tech_Number]
            .Cells(lRow, "B").Value = [Tech_First]
            .Cells(lRow, "C").Value = [Tech_Last]
            .Cells(lRow, "D").Value = [New_Number]
        End With
        xl.DisplayAlerts = False
        .Save
        .Close
        xl.DisplayAlerts = True
    End With
    
    Set rFound = Nothing
    Set xl = Nothing

 

[SkipVought]

Stuff like this can happen when you have BOTH text and numbers in the same column -- the numbers can be ignored because the query manager is only looking for TEXT.

Try this: CHANGE you phone numbers to TEXT by PREpending an APOSTROPHY. FORMAT will do NOTHING!!!

Skip,

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

 

[DarkOne72]

Not to upset you but do this on the excel sheet or in access; also how do you prepend it? I am sorry if this is a dumb question.

Thanks for yout time Skip

 

[SkipVought]

I assume that your source data is in Excel and you are updating an Access table.

So if your source data has a mixture of TEXT and NUMERIC data, then TILT!!! Ya gotta fix the source data to be consistant!

Could use something like this

sub Num2Txt()
'select any cell in this column and the entire column range will be fixed when you RUN

   dim r as range
   
   with selection.parent
     for each r in intersect(.usedrange, selection.entirecolumn)
        with r
           if isnumeric(.value) then
              .value = "'" & .value
           end if
        end with
     next 
   end with
end sub

Skip,

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

 

[DarkOne72]

The way I have it setup is that in access once the data changes and you hit the submit button it updates an excel file. With that there is a website that pulls from that excel file to display the information that was updated (using sql/asp.net)

 

[SkipVought]

So is the expected data in the Excel sheet?

Skip,

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

 

[DarkOne72]

When the data is changed in access and the submit button is hit it does pass everything to the excel sheet and is visible; however once I try to pull it up from the website it only shows the 1st 3 columns and not thenew number even though it is there. I also verified it is pulling the information on the "D" column via site as it still shows the data that I manually typed in for others but if access puts it in there it doesn't show. Sounds like a formatting thing to me as well but I don't know where to put that code you wrote above and/or fix the formatting. I checked the table in access and it is set to text with no formatiing as well as the form so i am not sure why it is passing it wrong.

 

[SkipVought]

So are you saying that the first 3 columns have data but the 4th column is COMPLETELY MISSING HEADING AND ALL?

Skip,

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

 

[DarkOne72]

For example: the site shows 4 columns with header of Tech# , First, Last, New_Number
The ones I manually typed in the excel sheet shows up fine for all 4 columns.
If I enter the data in on the access form and update it via access it puts all the data in all 4 columns in the excel sheet but it will not show up on the website it only shows the 1st 3 columns updated and not the 4th.(new_number)
I did open up the excel sheet and changed all columns to text but the ones that were updated via access will not format to text it still shows like this: (385) 555-9876 but the other ones that were manually typed in goes got: 3855559876

So maybe if there is a way in the code where it looks up and replaces or adds new in access to strip all formattingmaking it text only from the submit button when clicked on..?