Copy Formulas w/o workbook reference

Hello, thanks in advance for your support. I'm dealing with a pretty simple scenario but I'm stuck and I need some advice/help....

We have an old (but free&efficient) software that recovers data from any RS232 lab equipment to excel. This software always opens a new blank workbook based in the original excel template, no matter if we have defined other template as the default for new documents. I don't know how it manages to to this, but avoids the use of the default template we've set up in excel, and opens a new document based in the stock template of excel.

Once opened the new workbook, it copies the data that the equipment sends through the RS232 port, starting in the a1 cell of the first worksheet.

We're trying to program a macro to insert a worksheet with formulas in the workbook opened by the software. This worksheet with formulas is stored in other workbook (called "Formulas"). When we copy the worksheet, the formulas keep the references of the "Formulas" workbook, so they can't process the data obtained from the equipment, as they are in the newly opened workbook.

Hope I've explained myself properly, any help will be greatly appreciated,

Quote from 6StringJazzer

If I understand correctly, you are copying a sheet from a template file called Formulas into a new workbook. The sheet has formulas that reference another sheet in Formulas. After the copy, instead of referencing the sheet in the new workbook, they have an external references back to the Formulas workbook. You want those formulas to reference the corresponding sheet in the new workbook instead.

I have not found a way to do this during the copy/paste sheet operation, but since you are doing this with a macro you can add code to the macro to clean up after the sheet is pasted.

Code

    Worksheet("New Sheet").Cells.Replace _
      What:="[Formulas.xlsx]!", _
      Replacement:="", _
      LookAt:=xlPart, _
      FormulaVersion:=xlReplaceFormula2

Replace the red parts with the actual names.

Thank you, I was thinking about some kind of solution like this, but wondered if there's any kind of copy/paste parameters to update the references at the moment of pasting.

Nonetheless, this approach is perfect for me. Thank you very much.

Quote from Carim

Hello and Welcome to the Forum

Do you have access to some type of macro within your "old free & efficient software" ...

Hi Carim, unfortunately, I'm afraid not. The software is called WinTerm. I can post the software here as it seems not to be copyrighted at all. Actually, it's 16-bit software from back in the Windows 3.11 days (!) so maybe the copyright it's over (?). It runs at least in Windows 7 with "run as..." Windows 95/98. I'm attaching the manual.WinTerm.pdf

Before posting the software, I'd like to know the opinion of a moderator, thank you.

Regards

Quote from royUK

Doing this manually, I find

1. Use replace on the original formula sheet to remove =, replace with something like XYZ.
2. Copy and paste manually to the new sheet
3. Reverse the first line replacing XYZ with =

Obviously, close the template workbook without saving.

Thanks a lot, Roy.

Thanks everybody. As it frequently happens, things get harder when you think you're done.

I've managed to copy&paste formulas and remove the references thanks to the hints provided, programming a macro, that it should be always available as it's saved in my personal.xlsb file.

Incredingbly, when Excel is called from the WinTerm software, a new workbook ("Book1.xlsx") is created, but the personal.xlsb is not available, so the macro i programmed can't be called. If i try then to create a new macro and store it in the personal macro book, it says "Personal Macro Workbook in the startup folder must stay open for recording".

So no doubt: that software runs Excel without loading personal.xlsb

How could excel be run without loading my personal.xlsb? Moreover, if i start Excel manually, it loads personal.xlsb correctly!

Thank you very much in advance...