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
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