Create VBA for opening and replacing range of cells in Excel Worksheet (1 Viewer)

miacino

Registered User.
Local time
Today, 04:25
Joined
Jun 5, 2007
Messages
106
So I am taking data from a query "CHL" and pasting it into an Excel file. I got that to work fine.

I would also like to then in Column I - remove the wording "CHL" from any cells in that range. (my code in bold below)
I'm very new with VBA, so I'm sure I'm missing something obvious... but I can't seem to get it to work.

Any help is appreciated! Thanks!
Code below: ----------


Private Sub Command1_Click()

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Dim Range As Fields
Set excelapp = CreateObject("Excel.application", "")

Set qdf = dbs.QueryDefs("CHL")
qdf.Parameters("Enter Practice") = Me.Practice

Set rst = qdf.OpenRecordset()

excelapp.Visible = True
Set targetworkbook = excelapp.workbooks.Open("H:\CCCN\Supplemental Data\Practices\datasheet.xlsm")"
targetworkbook.worksheets("CHL").Range("A3").CopyFromRecordset rst

Range("I3:I500").Replace "CHL", ""

targetworkbook.Save
targetworkbook.Close

excelapp.workbooks.Close
excelapp.Quit

End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:25
Joined
Feb 19, 2002
Messages
42,970
copy/paste isn't the way to transfer the data. Use the TransferSpreadsheet method.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:25
Joined
Sep 21, 2011
Messages
14,038
Range should probably defined as range with a much better name?
RngReplace perhaps? as Range would be a reserved name
 

cheekybuddha

AWF VIP
Local time
Today, 11:25
Joined
Jul 21, 2014
Messages
2,237
First, add Option Explicit to the top of every code module.

Then, you need to declare your object variables. Also, the line Dim Range As Fields should cause an error.

Try something like:
Code:
Private Sub Command1_Click()

  Dim dbs As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim rst As DAO.Recordset
  Dim excelapp As Object
  Dim targetworkbook As Object
 
  Const xlPart As Integer = 2, _
        xlByColumns As Integer = 2
        
  Set dbs = CurrentDb
  Set qdf = dbs.QueryDefs("CHL")
  qdf.Parameters("Enter Practice") = Me.Practice
  Set rst = qdf.OpenRecordset()

  Set excelapp = CreateObject("Excel.application", "")
  With excelapp
    .Visible = True
    Set targetworkbook = .workbooks.Open("H:\CCCN\Supplemental Data\Practices\datasheet.xlsm")"
    With targetworkbook.worksheets("CHL")
      .Range("A3").CopyFromRecordset rst
      If .Range("I3:I500").Replace("CHL", "", xlPart, xlByColumns, False)
      .Save
      .Close
    End With
    .Workbooks.Close
    .Quit
  End With
 
  Set targetworkbook = Nothing
  Set excelapp = Nothing
  rst.Close
  Set rst = Nothing
  Set qdf = Nothing
  Set dbs = Nothing
 
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:25
Joined
Feb 19, 2002
Messages
42,970
So I am taking data from a query "CHL" and pasting it into an Excel file. I got that to work fine.
And in fact, that is what the code is doing.
 

miacino

Registered User.
Local time
Today, 04:25
Joined
Jun 5, 2007
Messages
106
First, add Option Explicit to the top of every code module.

Then, you need to declare your object variables. Also, the line Dim Range As Fields should cause an error.

Try something like:
Code:
Private Sub Command1_Click()

  Dim dbs As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim rst As DAO.Recordset
  Dim excelapp As Object
  Dim targetworkbook As Object

  Const xlPart As Integer = 2, _
        xlByColumns As Integer = 2
       
  Set dbs = CurrentDb
  Set qdf = dbs.QueryDefs("CHL")
  qdf.Parameters("Enter Practice") = Me.Practice
  Set rst = qdf.OpenRecordset()

  Set excelapp = CreateObject("Excel.application", "")
  With excelapp
    .Visible = True
    Set targetworkbook = .workbooks.Open("H:\CCCN\Supplemental Data\Practices\datasheet.xlsm")"
    With targetworkbook.worksheets("CHL")
      .Range("A3").CopyFromRecordset rst
      If .Range("I3:I500").Replace("CHL", "", xlPart, xlByColumns, False)
      .Save
      .Close
    End With
    .Workbooks.Close
    .Quit
  End With

  Set targetworkbook = Nothing
  Set excelapp = Nothing
  rst.Close
  Set rst = Nothing
  Set qdf = Nothing
  Set dbs = Nothing

End Sub
Thank you David!
I tried the above.
I doesn't seem to like:

If .Range("I3:I500").Replace("CHL", "", xlPart, xlByColumns, False)
 

miacino

Registered User.
Local time
Today, 04:25
Joined
Jun 5, 2007
Messages
106
Thank you David!
I tried the above.
I doesn't seem to like:

If .Range("I3:I500").Replace("CHL", "", xlPart, xlByColumns, False)
 

cheekybuddha

AWF VIP
Local time
Today, 11:25
Joined
Jul 21, 2014
Messages
2,237
I doesn't seem to like:

If .Range("I3:I500").Replace("CHL", "", xlPart, xlByColumns, False)

I'm not surprised! 😬

You can either replace If with Call

or add something like Then Debug.Print "Replacements made" to the end of the line.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:25
Joined
Feb 19, 2002
Messages
42,970
Set targetworkbook = excelapp.workbooks.Open("H:\CCCN\Supplemental Data\Practices\datasheet.xlsm")"
targetworkbook.worksheets("CHL").Range("A3").CopyFromRecordset rst
 

Users who are viewing this thread

Top Bottom