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
Rick RudolphAsked:
Who is Participating?
 
Rgonzo1971Commented:
BHI,

pls try

 
     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").UsedRange.Value = xl.activeworkbook.sheets("Sheet1").UsedRange.Value
        ' 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

Open in new window

Regards
0
 
Rick RudolphAuthor Commented:
Excellent, can you point me to a resource that would have explained this to me as well as other options for methods and workbooks?
0
 
Rgonzo1971Commented:
Could you be more precise
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rick RudolphAuthor Commented:
I had real problems trying to google methods I could use in an MSAcess VBA module to manipulate an Excel spreadsheet
0
 
Rgonzo1971Commented:
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)
0
 
Rick RudolphAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.