Rick Rudolph
asked on
MSAccess VBA - Within a Function, copy a sheet1 and then paste values back in sheet 1
I am trying to change all of the formulas in Sheet1 of an Excel Workbook to the values. After this, I will save the workbook under a new name. Can someone please help with the code below. I have commented out in Step 4 what I wrote that does not work.
Thank you
Function ExcelTest()
Dim xl As Object
'Step 1: Start Excel, then open the target workbook.
Set xl = CreateObject("Excel.Applic ation")
xl.workbooks.Open ("C:\Users\rrudo_000\Dropb ox\TBCO\BM A_OtherInf o\Cash_Tem plate.xlsx ")
'Step 2: Make Excel visible
xl.Visible = True
'Step 3: Refresh the data
xl.activeworkbook.Refresha ll
'Step 4: Save the Cash_Template as a different file name
' xl.activeworkbook.sheets(" Sheet1").c opy
' xl.activeworkbook.sheets(" Sheet1").P asteSpecia l xlPasteValues
xl.activeworkbook.saveas ("C:\Users\rrudo_000\Dropb ox\TBCO\BM A_OtherInf o\TestNewC ash_Templa te.xlsx")
xl.activeworkbook.Close (True)
xl.Quit
'Step 5: Memory Clean up.
Set xl = Nothing
End Function
Thank you
Function ExcelTest()
Dim xl As Object
'Step 1: Start Excel, then open the target workbook.
Set xl = CreateObject("Excel.Applic
xl.workbooks.Open ("C:\Users\rrudo_000\Dropb
'Step 2: Make Excel visible
xl.Visible = True
'Step 3: Refresh the data
xl.activeworkbook.Refresha
'Step 4: Save the Cash_Template as a different file name
' xl.activeworkbook.sheets("
' xl.activeworkbook.sheets("
xl.activeworkbook.saveas ("C:\Users\rrudo_000\Dropb
xl.activeworkbook.Close (True)
xl.Quit
'Step 5: Memory Clean up.
Set xl = Nothing
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Could you be more precise
ASKER
I had real problems trying to google methods I could use in an MSAcess VBA module to manipulate an Excel spreadsheet
AS soon as you have set the Excel Application object
Set xl = CreateObject("Excel.Applic ation") ' = Application in excel
the Excel Object is open to you so you can search for Excel VBA ( no need to reference Access in your search)
Set xl = CreateObject("Excel.Applic
the Excel Object is open to you so you can search for Excel VBA ( no need to reference Access in your search)
ASKER
Thank you
ASKER