VB to Search and replace in excel from access 1

[DarkOne72]

Hello all,

I was wondering if someone could assist me in creating a command button in access to when it is clicked it will open and search an excel file (column A) based on textbox1 on access form, if found it will change the cell 3 over to the right in the excel document (column d but on the row the item was found in A) from a textbox2 on the access form. Then save and close the excel sheet.

The excel sheet is on a sharedrive by the way.

Thanks for you assistance in advance.

 

[SkipVought]

hi,

Here's how you can generate most of the raw code.

Open Excel.

Turn on the macro recorder to record:

Open a workbook

Find a string ( in col A)

3 cells to the right (column D) change the value

turn off the macro recorder and observe the code in the VB Editor (alt+F11)

Copy and paste into your Access VB module and modify. Post the code HERE for help in modifying.



Skip,

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

 

[DarkOne72]

I did the macro but not sure how to get access to do it. I configured the "areas when I did the search to the field name in access. (I think that was right to do)

Here is the code:

Private Sub cmdReplace_Number_Click()
    Workbooks.Open FileName:= _
        "\\MyShare\Inetpub\[URL unfurl="true"]wwwroot\TechDirectory\App_Data\Tech_Test.xls"[/URL]
    Range("A1").Select
    Cells.Find(What:=[Tech_Number], After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("D4").Select
    ActiveCell.FormulaR1C1 = [New_Number]
    Range("D5").Select
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub
[code/]

 

[DarkOne72]

Oh forgot to mention, I am not sure on how to do the ranges either that states the A1 and D4

 

[SkipVought]

Private Sub cmdReplace_Number_Click()
    Dim xl As Object, rFound As Object
    
    Set xl = CreateObject("Excel.Application")
    
    With xl.Workbooks.Open(Filename:= _
    "\\MyShare\Inetpub\[URL unfurl="true"]wwwroot\TechDirectory\App_Data\Tech_Test.xls")[/URL]
    
        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 = 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

Skip,

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

 

[DarkOne72]

Thank you for the fast reply...I inserted the code and tried to execute it by command button and I get and error box saying "Invalid Use of Object" with "Nothing" highlighted here : If Not rFound = Nothing Then

Thanks again

 

[SkipVought]

so sorry

If Not rFound IS Nothing Then

Skip,

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

 

[DarkOne72]

Okay, I corrected that and now I am getting an error
"Runtime error '9'
Subscript out of range
...and this is highlighted:
Set rFound = .Columns(1).Find( _
What:=[Tech_Number], _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Sorry for not being as knowledgeable about this...

 

[SkipVought]

Unless you have set a reference to the Microsoft Excel Object Library, then NONE of the Excel CONSTANTS, like xlFormulas or xlWhole, are UNDEFINED!!!

So either set a reference in Tools > References or substitute the appropriate VALUES for each of these constants.

Skip,

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

 

[DarkOne72]

I am not sure where to go and set the references in Access 2010 for the Excel Object Library or how to do the other you mentioned...

 

[SkipVought]

In your MS Access VB Editor, Tools > References...

Skip,

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

 

[DarkOne72]

Hehe..Yeah I just found it..! Thank you for all of your help it works like a charm!

I really appreciate it!

 

[DarkOne72]

I was wondering, now that I have everything in place, what if the code did not find the item it was searching for?
How would I added the information for the 4 cells in the excel sheet from the access form? (Columns would be A,B,C,D)

I assume it would go in the

            If Not rFound = Nothing Then
            'found the value
                .Cells(rFound.Row, "D").Value = [New_Number]
            Else
            'did not find the value
            End If
        End With
[code/]

 

[DarkOne72]

I meant to say, 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?

Thanks in advance.....