Export to Excel, formula instead of value

Microsoft Excel 2003 (full product)
November 15, 2010 at 00:25:01
Specs: Windows XP, Intel 2x 2GHz
Hello, I have a very simple application which exports its data to an excel file. When there's a new PC using this app, the app exports the data in format such as =CTXT(cnum(17);0). I would need to find out what to do to export the data in the correct format of a value - number, in this case 17. I guess there's a template to be used in the solution but not sure exactly what to do. Thanks for help.

See More: Export to Excel, formula instead of value

Report •

#1
November 15, 2010 at 07:01:50
simple application which exports its data to an excel file

What is this simple application?
I would guess that is where your problem lies.

There may be a macro in use that needs to be copied to the new pc.

I guess there's a template to be used in the solution but not sure exactly what to do.
Why do you belive that there is a template involved?

Right now we don't have enough info.

MIKE

http://www.skeptic.com/


Report •

#2
November 16, 2010 at 01:17:14
Hi mmcconaghy, thanks for your reply. This app is a very simple Intranet one however I cannot change the application itself. It creates an EuroFactor report of transmitted items and amounts.
One of my predecessors was solving that with help of a template but no more information is available and this guy's unreachable. That's why I am aware of a template solution.

Report •

#3
November 16, 2010 at 05:22:12
If you have this working on other PC's, why can't you copy the relevant template files over to the new PC?

Sorry, I have not other ideas, perhaps someone else will.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
November 16, 2010 at 06:10:14
the app exports the data in format such as =CTXT(cnum(17);0).
in this case 17

Is it just coincidence that the number in the formula is 17
and the number you need is 17?
Or, does the exporting software return all your data in the form of a formula like this?

MIKE

http://www.skeptic.com/


Report •

#5
November 16, 2010 at 06:42:23
This is an example. It returns all numbers in such format. For example when the needed value is 17, we see =CTXT(cnum(17);0)
So in general it looks like this - needed value X, Excel shows =CTXT(cnum(X);0)

Report •

#6
November 16, 2010 at 07:02:04
So, the format is always =CTXT(cnum(X);0) with the required data as the X?

Processing the data between the export software and importing it to Excel would probably be he simplest solution.

A Macro or batch script would probably work.

MIKE

http://www.skeptic.com/


Report •

#7
November 16, 2010 at 11:06:47
If your format is always =CTXT(cnum(X);0) with the required data as the X, then try this formula:

With the string:

=CTXT(cnum(17);0)

in cell A1

put this formula in cell B1

=MID(A1,FIND("(",A1,7)+1,FIND(")",A1,7)-FIND("(",A1,7)-1)

Again, the formula is dependent on the format always being as shown.

MIKE

http://www.skeptic.com/


Report •

#8
November 18, 2010 at 00:28:53
Thanks for the tips. However it doesn't cover my requirement, as the export
of sheets needs to be automated... What I have found out is that I have a template.xla file which is a part of a solution used earlier but no idea what to do further.

Report •

#9
November 18, 2010 at 06:49:30
I have a template.xla file which is a part of a solution

How is the template.xla file configured?
Are there formula in the cells?
Is there a macro attached?
What version of Excel are you using?

What are the exact steps in the import process?
Is there an inbetween step where the application exports to a .csv type file first, then is imported to Excel?

MIKE

http://www.skeptic.com/


Report •

#10
November 19, 2010 at 08:26:17
I'm using MS Excel 2003. The Module 1 in the macro in .xla file displays the following code:

Function ctxt(ByRef t As Double, ByRef t2 As String) As String

ctxt = t
End Function

Function cnum(ByVal t As Double) As Double


cnum = CDbl(t)

End Function

Function cnum2(ByVal t As Double, ByVal t2 As Double) As Double

cnum2 = t + t2
End Function
---
There is no inbetween step in the middle, the application just exports the data to an Excel file with the wrong format.

huraczech


Report •

#11
November 19, 2010 at 08:49:35
Unfortunately, my Macro skills are about nill, hopefully someone with more expertise will be able to help out.

MIKE

http://www.skeptic.com/


Report •

#12
November 19, 2010 at 14:36:17
I'd like to see this export data, myself. What does the raw export look like? Not what Excel gives you, but what's in the file? Text?

How To Ask Questions The Smart Way


Report •

#13
November 24, 2010 at 01:57:27
Hi Razor, I am not sure what do you mean. There's no graphic, just formatted text in cells.

Report •

#14
November 24, 2010 at 07:54:01
It creates an EuroFactor report of transmitted items and amounts.

Is this a separate file that is then imported to Excel?

How do you get the data from the EuroFactor report into Excel?
Are there any intermediate steps?
What steps do you take?


I believe Razor is looking for an example of your data.

Read this How To:

http://www.computing.net/howtos/sho...

Then post a sample of your spreadsheet, several rows & columns
so Razor can see what it actually looks like.

MIKE

http://www.skeptic.com/


Report •

#15
November 26, 2010 at 15:40:03
Kind of, but I mean open the file with Notepad.

Basically, I'm trying to find out if it's a botched spreadsheet, a botched XML spreadsheet, or comma separated value file.

How To Ask Questions The Smart Way


Report •

#16
November 29, 2010 at 00:02:11
Hello Razor, when I open the data with Notepad it's just a text and values which appear in the sheet. When I open the file with notepad I can see standard HTML format, see below:

<HTML>
<HEAD>

</HEAD>
<BODY>
<TABLE align='left' width='80%'>
<TR><TD><TABLE align='left' width=60%>
<TR><TD class='text'>
Report for
</TD></TR><TR><TD class='text'> </TD></TR>
<TR><TD width='30%' class='text'>Date : 25/10/2010</TD>
<TD class='text'>Devise : EUR</TD></TR>
<TR><TD class='text'> </TD></TR>
<TR><TD align='right' style="color:'#FF3333'" width='70%' height=30px class='text'>1 Transmitted files</TD>
<TD align='center' width='30%' height=32px class='text' noborder>
</TD></TR>
</TABLE></TD></TR>
<TR><TD> </TD></TR>
<TR><TD><TABLE border=1 align='left' width='60%'>
<TR><TD align='right' style="color:'#FF3333'" width='70%' height=30px class='text'>Number of transmitted files</TD>
<TD align='center' width='30%' height=30px class='text'>Subrogeant 22
XXXXXX XX
</TD></TR>
<TR><TD align='right' width='70%' height=30px class='text'>TIC subfile 1</TD>
<TD align='center' width='30%' height=30px style="text-align:right;background-color:'#FFFF99'" class='textFormField'>
=CTXT(cnum(63);0)</TD></TR>
<TR><TD align='right' width='70%' height=30px class='text'>TAN subfile 2</TD>
<TD align='center' width='30%' height=30px style="text-align:right;background-color:'#FFFF99'" class='textFormField'>
=CTXT(cnum(9);0)</TD></TR>
<TR><TD align='right' width='70%' height=30px class='text'>TCN subfile 3</TD>
<TD align='center' width='30%' height=30px style="text-align:right;background-color:'#FFFF99'" class='textFormField'>
=CTXT(cnum(17);0)</TD></TR>
<TR><TD align='right' width='70%' height=30px class='text'>TOC subfile 4</TD>
<TD align='center' width='30%' height=30px style="text-align:right;background-color:'#FFFF99'" class='textFormField'>
=CTXT(cnum(5);0)</TD></TR>
<TR><TD align='right' width='70%' height=30px class='text'>TOD subfile 5</TD>
<TD align='center' width='30%' height=30px style="text-align:right;background-color:'#FFFF99'" class='textFormField'>
=CTXT(cnum(21);0)</TD></TR>
<TR><TD align='right' width='70%' height=30px class='text'>TAO subfile 6</TD>
<TD align='center' width='30%' height=30px style="text-align:right;background-color:'#FFFF99'" class='textFormField'>
=CTXT(cnum(0);0)</TD></TR>
<TR><TD align='right' width='50%' height=30px class='text'>Total number of transmitted files</TD>
<TD align='center' width='50%' height=30px style="text-align:right;background-color:'#CCFFFF'" class='textFormField'>=CTXT(CNUM(b9+b10+b11+b12+b13+b14);0)</TD></TR>
</TABLE></TD></TR>
<TR><TD> </TD></TR>
<TR><TD><TABLE border=1 align='left' width='60%'>
<TR><TD align='right' style="color:'#FF3333'" width='70%' height=30px class='text'>Transmitted amnt</TD>
<TD align='center' width='30%' height=30px class='text'>Subrogeant 22
XXXXXX XX
</TD></TR>
<TR><TD align='right' width='70%' height=30px class='text'>TIC subfile 1</TD>
<TD align='center' width='30%' height=30px style="text-align:right;background-color:'#FFFF99'" class='textFormField'>
=CTXT(cnum(2355062,45);2)</TD></TR>
<TR><TD align='right' width='70%' height=30px class='text'>TAN subfile 2</TD>
<TD align='center' width='30%' height=30px style="text-align:right;background-color:'#FFFF99'" class='textFormField'>
=CTXT(cnum(37764,64);2)</TD></TR>
<TR><TD align='right' width='70%' height=30px class='text'>TCN subfile 3</TD>
<TD align='center' width='30%' height=30px style="text-align:right;background-color:'#FFFF99'" class='textFormField'>
=CTXT(cnum(357267,53);2)</TD></TR>
<TR><TD align='right' width='70%' height=30px class='text'>TOC subfile 4</TD>
<TD align='center' width='30%' height=30px style="text-align:right;background-color:'#FFFF99'" class='textFormField'>
=CTXT(cnum(180509,76);2)</TD></TR>
<TR><TD align='right' width='70%' height=30px class='text'>TOD subfile 5</TD>
<TD align='center' width='30%' height=30px style="text-align:right;background-color:'#FFFF99'" class='textFormField'>
=CTXT(cnum(41643,22);2)</TD></TR>
<TR><TD align='right' width='70%' height=30px class='text'>TAO subfile 6</TD>
<TD align='center' width='30%' height=30px style="text-align:right;background-color:'#FFFF99'" class='textFormField'>
=CTXT(cnum(,00);2)</TD></TR>
<TR><TD align='right' width='50%' height=30px class='text'>Total transmitted amounts (A)</TD>
<TD align='center' width='50%' height=30px style="text-align:right;background-color:'#CCFFFF'" class='textFormField'>=CTXT(cnum(b19-b20-b21-b22+b23+b24);2)</TD></TR>
</TABLE>
</TD></TR>
</TABLE>
</BODY>
</HTML>


Report •

#17
November 29, 2010 at 05:15:57
So it IS just plain text. That's usable.

I'm getting a circular reference from the fixed version, but the referenced cells aren't different from the unmodified version, so I'm just going to assume it's caused by the anonymizing.

Instructions:
1) Save the text after these steps as a .vbs file (ie fixSheet.vbs)
2) Drag and drop the files to be fixed over the fixSheet.vbs icon
3) Open the .Fixed. version(s)

Set patStart = getRegExp("CTXT\(cnum\(")
Set patEnd = getRegExp("\);[0-9]+\)")
Set fso = CreateObject("Scripting.FileSystemObject")
For Each arg In WScript.Arguments
  file = fso.OpenTextFile(arg).ReadAll
  outName = fso.BuildPath(fso.GetParentFolderName(arg), _
    fso.GetBaseName(arg) & ".Fixed." & fso.GetExtensionName(arg))
  fso.OpenTextFile(outName, 2, True).WriteLine _
    patEnd.Replace(patStart.Replace(file, ""), "")
Next 'arg
WScript.Echo "Done" & vbNewLine & _
  "Processed " & WScript.Arguments.Count & " files"
WScript.Quit 

Function getRegExp(sPat) 'As RegExp
  Set getRegExp = New RegExp
  With getRegExp
    .IgnoreCase = True
    .Global = True
    .Pattern = sPat
  End With
End Function

How To Ask Questions The Smart Way


Report •

#18
December 13, 2010 at 06:44:23
Thanks a lot Razor - tried it, worked great except the last row - I just have the #VALUE! error.
Would you be able to find out a way how to skip this step and be able to export from the intranet app directly to Excel with the values?

Report •

Ask Question