Link to home
Start Free TrialLog in
Avatar of Rick Rudolph
Rick RudolphFlag for United States of America

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.Application")
        xl.workbooks.Open ("C:\Users\rrudo_000\Dropbox\TBCO\BMA_OtherInfo\Cash_Template.xlsx")
     
    'Step 2:  Make Excel visible
       xl.Visible = True
     
    'Step 3:  Refresh the data
       xl.activeworkbook.Refreshall
     
    'Step 4:  Save the Cash_Template as a different file name
       ' xl.activeworkbook.sheets("Sheet1").copy
       ' xl.activeworkbook.sheets("Sheet1").PasteSpecial xlPasteValues
       xl.activeworkbook.saveas ("C:\Users\rrudo_000\Dropbox\TBCO\BMA_OtherInfo\TestNewCash_Template.xlsx")
       
       xl.activeworkbook.Close (True)
        xl.Quit
     
    'Step 5:  Memory Clean up.
       Set xl = Nothing
     





End Function
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rick Rudolph

ASKER

Excellent, can you point me to a resource that would have explained this to me as well as other options for methods and workbooks?
Avatar of Rgonzo1971
Rgonzo1971

Could you be more precise
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.Application")  ' = 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)
Thank you