Create VBA for opening and replacing range of cells in Excel Worksheet

[miacino]

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]

copy/paste isn't the way to transfer the data. Use the TransferSpreadsheet method.

 

[Gasman]

Range should probably defined as range with a much better name?
RngReplace perhaps? as Range would be a reserved name

 

[cheekybuddha]

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:

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

 

[cheekybuddha]

copy/paste isn't the way to transfer the data

Who was copy'n'pasting?

 

[Pat Hartman]

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.

 

[cheekybuddha]

Where in the code? - I'm being blind today

 

[miacino]

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:

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]

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

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

 

[cheekybuddha]

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]

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

 

[cheekybuddha]

OK, I thought you were talking about copy and paste.