• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Workbook --> Hard Values

B

Berry

New Member
Hi All,

As it turns out, Indesign is not able to import the results of relatively complicated formulas, especially formulas for text write ="Hello"&"World", for example. As far as I can judge (but I might be wrong), the only way to make my excel file fully compatible for import in Indesign is to convert all results into hard values. Correct?

Question therefore is: How to make a macro button, that:
1. Creates a new workbook/excel file with an exact copy of the original excel file, incl. all worksheets.
2. Converts all values of this newly created workbook into hard values.
3. Saves this newly created workbook. Any new file should be written over the previous file, as otherwise the linking with the Indesign file will get messed up.

Looking forward to your reply!
Thank you in advance!
Berry
 
Berry said:
Hi All,

As it turns out, Indesign is not able to import the results of relatively complicated formulas, especially formulas for text write ="Hello"&"World", for example. As far as I can judge (but I might be wrong), the only way to make my excel file fully compatible for import in Indesign is to convert all results into hard values. Correct?

Question therefore is: How to make a macro button, that:
1. Creates a new workbook/excel file with an exact copy of the original excel file, incl. all worksheets.
2. Converts all values of this newly created workbook into hard values.
3. Saves this newly created workbook. Any new file should be written over the previous file, as otherwise the linking with the Indesign file will get messed up.

Looking forward to your reply!
Thank you in advance!
Berry
Click to expand...
Hi Assuming that your workbook is open along with your macro workbook
My Code would be
Code: 
Sub ChangeFormulasToValues()

Dim WB As Workbook, WS As Worksheet

For Each WB In Workbooks
    If WB.Name <> ThisWorkbook.Name Then
        For Each WS In WB.Worksheets
            WS.UsedRange.Value = WS.UsedRange.Value
        Next WS
    End If
Next WB
End Sub

With Regards
Rudra
 
Last edited by a moderator:
Hi Rudra,

Thank you so much for your quick reply. The code you provided me with is almost exactly what I was looking for, except for the fact that I will need to open the file-to-be-copied to by myself in advance. Could you also come up with a code that first automatically creates a new file (or overwrites an old one, if one already exists), before copying the hard values to that file? So, a one-stop-go solution.

Once again, thank you for your help!
Kind regards,
Berry Schrijen
 
You must log in or register to reply here.
Back
Top