UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Change All Formulas To Values, Access 2016    
 
   
aggiemarine07
post Aug 20 2019, 09:16 AM
Post#1



Posts: 48
Joined: 7-January 11



So I have a working macro (code below) that successfully copies a worksheet from another workbook, adjust the formulas, and breaks the links from the previous workbook (all while accounting for if the workbook is protected or not). However, I am now trying to change the values in the newly copied sheet from formulas to value's and am having difficulty implementing the new code.

I searched the internet and found some simple code that will do the job but its not working (i'll highlight below where I feel it should be implemented) as I keep getting an "end with without with statement" error but it has both the with and the end with; can a With Statement not be within another With statement? Is that bad coding?

End goal: After the new worksheet has been copied and formulas have been adjusted to the new sheet, to then change those formulas to values.

What am I missing? What noob mistakes did I make?

--------------------

Code I want to implement that will change all formulas to values:
CODE
with range("A1").currentregion
    .value = .value
end with


Working Code:

CODE
Sub Command0_Click()
Dim xl As Excel.Application
Dim wbMaster As Excel.Workbook
Set xl = New Excel.Application
Set wbMaster = xl.Workbooks.Open(CurrentProject.path & "\ProjectBabelfish")

Dim wsCopy As Excel.Worksheet
Set wsCopy = wbMaster.Worksheets("Babelfish")

Dim sFolderPath As String
sFolderPath = wbMaster.path & "\PLOGs\"
If Right(sFolderPath, 1) <> "\" Then sFolderPath = sFolderPath & "\"

Dim sFileName As String
sFileName = Dir(sFolderPath & "*.xlsx")

'Disable screenupdating (to prevent "screen flickering" so macro runs smoother)
'Disable alerts (to suppress "Are you sure?" prompts during worksheet deletion)
xl.ScreenUpdating = False
xl.DisplayAlerts = False

'Begin loop through files in the folder
Do While Len(sFileName) > 0

    Dim sWBOpenPassword As String
    Dim sWBProtectPassword As String
    Select Case sFileName
        'Specify workbook names that require passwords here
        Case "book1.xlsx"
            sWBOpenPassword = "550"
            sWBProtectPassword = "550"

        'If different books require different passwords, can specify additional names with their unique passwords
        Case "Book3.xlsx"
            sWBOpenPassword = "book3openpassword"
            sWBProtectPassword = "book3protectionpassword"

        'Keep specifying excel file names and their passwords until completed
        Case "Book10.xlsx", "Book257.xlsx"
            sWBOpenPassword = "GenericOpenPW2"
            sWBProtectPassword = "GenericProtectPW2"

        'Case Else will handle the remaining workbooks that don't require passwords
        Case Else
            'MsgBox sFileName & " has a wierd password"
            sWBOpenPassword = "550"
            sWBProtectPassword = "550"

    End Select

    'Open file using password (if any)
    On Error Resume Next
    With xl.Workbooks.Open(sFolderPath & sFileName, UpdateLinks:=False, Password:=sWBOpenPassword)
        If Err.Number = 1004 Then MsgBox "The workbook " & sFileName & " is at a middle school dance."
        Dim bProtectedWB As Boolean
        bProtectedWB = False    'Reset protected wb check to false

        'Check if workbook is protected and if so unprotect it using the specified protection password
        If .ProtectStructure = True Then bProtectedWB = True
        If bProtectedWB = True Then .Unprotect sWBProtectPassword
            .Sheets(1).Name = "PLOG"
            
        On Error Resume Next    'Suppress error if copied worksheet does not yet exist
        .Worksheets(wsCopy.Name).Delete 'Delete existing sheet if it exists
        On Error GoTo 0         'Remove "On Error Resume Next" condition
    
            wsCopy.Copy After:=.Worksheets(.Worksheets.Count)   'Copy template into the workbook
            .Worksheets(wsCopy.Name).Cells.Replace wbMaster.Name, .Name 'Change references from master workbook to current workbook

'---->code should be placed here correct? Since I want to change the formulas to values after the sheet has been copied and before it protects the workbook again
with .Worksheets(wsCopy.Name).Range("A1:M10").currentregion
    .value = .value
end with
            
            'If workbook was protected, reprotect it with same protection password
            If bProtectedWB = True Then .Protect sWBProtectPassword
    
            'Close file and save the changes
        
        If Not IsEmpty(varLinks) Then
            For i = 1 To UBound(varLinks)
                .BreakLink _
                    Name:=varLinks(i), _
                    Type:=xlLinkTypeExcelLinks
            Next i
        End If
        
        .Close True

    End With
      
    sFileName = Dir 'Advance to next file in the folder
    
Loop

'Re-enable screenupdating and alerts
xl.ScreenUpdating = True
xl.DisplayAlerts = True
xl.Quit

MsgBox "Added Tab to all PLOG files"

End Sub
Go to the top of the page
 
June7
post Aug 20 2019, 10:52 PM
Post#2



Posts: 811
Joined: 25-January 16



With certainly can be nested and nothing wrong with doing it. I don't see any missing End statements. I tested the .Value = .Value and that works.

I copy/pasted your entire procedure into a module and it does compile. I did find variables varLinks and i are not declared.

The procedure worked until you added the inner With? Must be something wrong with way the new worksheet is referenced.



This post has been edited by June7: Aug 20 2019, 11:52 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
June7
post Aug 20 2019, 11:53 PM
Post#3



Posts: 811
Joined: 25-January 16



If you want to provide the Excel file, I will do some more tests.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
aggiemarine07
post Aug 21 2019, 07:17 AM
Post#4



Posts: 48
Joined: 7-January 11



@June7 thanks so much for your help, I did manage to get the code to run without error but now its simply not doing what I need it to at this point: change the formulas to their values in the worksheet of "Babelfish" aka wsCopy.Name. When I open the excel file after it finishes, I still see the formulas in the cells instead of their values.

Is there an easier method to accomplish this?

I have attached my DB and the corresponding excel files for testing.
This post has been edited by aggiemarine07: Aug 21 2019, 07:19 AM
Attached File(s)
Attached File  Project_Babelfish.zip ( 329.7K )Number of downloads: 7
 
Go to the top of the page
 
PaulBrand
post Aug 21 2019, 07:45 AM
Post#5



Posts: 1,719
Joined: 4-September 02
From: Oxford UK


I think you should copy the range then

CODE
with .Worksheets(wsCopy.Name).Range("A1:M10").Copy
    .Worksheets(wsCopy.Name).Range("A1").PasteSpecial Paste:=xlPasteValues
end with

This post has been edited by PaulBrand: Aug 21 2019, 07:51 AM

--------------------
Paul
Go to the top of the page
 
June7
post Aug 21 2019, 02:34 PM
Post#6



Posts: 811
Joined: 25-January 16



I am surprised code works at all because there is a space in folder name and that space in not in the coded folder name. But apparently the code handles that.

I ran code with the addition of .Value = .Value code and it executes without error. I even deleted worksheet Babelfish first. Worksheet is created and it has no formulas, replaced with values. Which worksheet are you looking at with formulas?

Haven't figured out how workbook ProjectBabelfish.xlsm figures in this process. I see it gets referenced in code but it is neither modified nor its data copied. Perhaps you can clarify.

This post has been edited by June7: Aug 21 2019, 03:04 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
aggiemarine07
post Aug 21 2019, 04:41 PM
Post#7



Posts: 48
Joined: 7-January 11



@PaulBrand - tried your method and it still did not work

@June7 - the code should go through every excel file within the "PLOG" folder and do this:
1) open first workbook (EX:WORKBOOK1.XLSX) in "PLOG" folder
2) test whether it is protected or not
2a) if protected, then it will unprotect it
3) Copy worksheet "Babelfish" from "Project Babelfish.xlsx" in root folder into the open workbook (EX: WORKBOOK1.XLSX)
4) Update the formulas in the copied worksheet from the master workbook (PROJECT BABELFIS.XLSX) to the open workbook (WORKBOOK1.XLSX)
5) **This is where I want it to copy everything and paste as values (keeping the formatting from the PROJECT BABELFISH.XLSX file)
6) Break the links to the previous workbook (PROJECT BABELFISH.XLSX)
7) Protect the workbook, Save, Close, Quit

The end end goal that I want it do (which is outside of the scope of my original question but since we're here), is to keep all of the formatting that I designed in the PROJECT BABELFISH.XLSX file and have it applied to every excel file in the "PLOG" folder. This is why I want to do the whole paste as values method, unless you can think of a better way to accomplish this (I am open to suggestions).

Let me know if this doesnt make sense; thanks so much for all your help with this!
Go to the top of the page
 
June7
post Aug 21 2019, 11:34 PM
Post#8



Posts: 811
Joined: 25-January 16



Probably doesn't make difference, but ProjectBabelfish is an xlsm, not xlsx.

I understand the process and the code runs. Worksheet in PLOGs workbook is created with values, no formulas. However, you have not clarified what data the new worksheet should have.

ProjectBabelfish.xlsm shows 9 records. The new worksheet shows only 1 record. If this is correct, then the code works.

The PLOGs workbook is not protected because it wasn't to start with.

This post has been edited by June7: Aug 21 2019, 11:35 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
aggiemarine07
post Aug 22 2019, 07:26 AM
Post#9



Posts: 48
Joined: 7-January 11



I want the formulas in the new workbook, which the worksheet is being copied to (i.e. WORKBOOK1.XLSX) to have the values; see attached image, this is the result that I do NOT want.

Is there a way I step through the code and have access show me line by line what its doing? I feel like its working but somewhere else.
Attached File(s)
Attached File  Capture2.PNG ( 65.84K )Number of downloads: 2
 
Go to the top of the page
 
aggiemarine07
post Aug 22 2019, 07:51 AM
Post#10



Posts: 48
Joined: 7-January 11



Well I ended up solving it (I think) by using the below code:

CODE
            .Worksheets("Babelfish").Activate
            .Worksheets("Babelfish").Range("A2:M3").Select
            .Worksheets("Babelfish").Range("A2:M3").Copy
            .Worksheets("Babelfish").Range("A2:M3").PasteSpecial Paste:=xlPasteValues
Go to the top of the page
 
PaulBrand
post Aug 22 2019, 07:58 AM
Post#11



Posts: 1,719
Joined: 4-September 02
From: Oxford UK


I'm glad you got it sorted :-)

By the way you can add a breakpoint anywhere in your code (F9 will set a breakpoint on the cursor line) and step through F8 or Run F5

--------------------
Paul
Go to the top of the page
 
June7
post Aug 22 2019, 04:10 PM
Post#12



Posts: 811
Joined: 25-January 16



Glad you found solution. Interesting that your original code works for me.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st September 2019 - 06:37 PM