Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Replacing text in cell via Access

Posted on 2011-05-10
4
Medium Priority
?
228 Views
Last Modified: 2012-05-11
vba...excel 2003
Access 2003

I have function that currently replaces a column of data via looping...

What I need:
In excel.  what ever the current cell is ,  TO THE LEFT OF THE CURRENT CELL, is the value I need to check. If during the Access loop it finds a match it replaces the value with that text...and the focus goes back to the current cell.


Sub ReplaceColText()
   Dim dB As DAO.Database
   Dim txtFind As String
   Dim txtReplace As String
Dim stDatabasePath As String
Dim Connect As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim Source As String
stDatabasePath = "C:\Program Files\Crs Enterprise\iSearch\replace97.mdb"

'On Error GoTo HANDLEERROR
Connect.ConnectionString = "DSN=replace97;UID=user;PWD=user;"
Connect.Open

Source = "SELECT txtfind, txtreplace from tblFindReplace "
rs.Open Source, Connect, adOpenKeyset, adLockOptimistic
   rs.MoveFirst
   Do While Not rs.EOF
       txtFind = rs("txtFind")
       txtReplace = rs("txtReplace")
       Selection.Replace What:=txtFind, Replacement:=txtReplace, LookAt:=xlWhole, _
       SearchOrder:=xlByColumns, MatchCase:=False
       rs.MoveNext
   Loop
End Sub



0
Comment
Question by:Fordraiders
  • 2
  • 2
4 Comments
 
LVL 10

Expert Comment

by:SANTABABY
ID: 35733003
You need to have a handle to your excel application inside your access code (I'm not going into the details, but I can provide examples if needed).

If the variable (handle) for excel app ip "exl", use something like the following within your access code loop:
        If exl.Cells(exl.ActiveCell.Row, exl.ActiveCell.Column - 1) = txtFind  Then
            exl.ActiveCell = txtReplace
        End If

The focus remains on the activecell( which was in focus prior to the execution of the above code).
See below for a sample code (in MSProject VBA)  accessing excel from MSProject.
Sub test_app()
    Dim exl As Excel.Application
    Dim wb As Excel.Workbook
    Dim xlSheet1 As Excel.Worksheet
    
    Set exl = New Excel.Application
    exl.Visible = True
    AppActivate "Microsoft Excel"
    
        Set wb = exl.Workbooks.Add()
    
        Set xlSheet1 = wb.Worksheets.Add()
        
        xlSheet1.Cells(4, 4) = "X"
        xlSheet1.Cells(4, 5).Select
   
        If exl.Cells(exl.ActiveCell.Row, exl.ActiveCell.Column - 1) = "X" Then
            exl.Cells(exl.ActiveCell.Row, exl.ActiveCell.Column - 1) = "XX"
        End If
    MsgBox exl.ActiveCell.Row & ":" & exl.ActiveCell.Column - 1
End Sub

Open in new window

0
 
LVL 10

Accepted Solution

by:
SANTABABY earned 2000 total points
ID: 35733142
Sorry for the typo, code should look like:
        If exl.Cells(exl.ActiveCell.Row, exl.ActiveCell.Column - 1) = txtFind  Then
            exl.Cells(exl.ActiveCell.Row, exl.ActiveCell.Column - 1) = txtReplace
        End If

0
 
LVL 3

Author Comment

by:Fordraiders
ID: 35733306
this will do it:


Dim mfg As String
Set targ = Application.ActiveCell
mfg = Application.ActiveCell.Offset(0, -1).Value
Dim dB As DAO.Database
Dim txtFind As String
Dim txtReplace As String
Dim rs As DAO.Recordset
Dim Source As String
Set dB = OpenDatabase("C:\Program Files\Crs Enterprise\iSearch\replace97.mdb")
Source = "SELECT txtfind, txtreplace from tblFindReplace "
Set rs = dB.OpenRecordset(Source, dbOpenDynaset)
   rs.MoveFirst
   Do While Not rs.EOF
       txtFind = rs("txtFind")
       txtReplace = rs("txtReplace")
       If mfg = txtFind Then
       mfg = txtReplace
       Application.ActiveCell.Offset(0, -1).Value = mfg
       rs.Close
       dB.Close
       Set dB = Nothing
       Set rs = Nothing
       Exit Do
     
End If
       rs.MoveNext

Loop
0
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 35746485
Thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question