How to paste a data file into a spreadsheet including formulas?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use Excel 2000, and I have a spreadsheet that I have been updating
by hand. The data is getting fairly large now, so I am in the process
of writing a perl script to generate a text file containing the data,
separated by tabs, so that I can just copy everything in the text file
and paste it into the spreadsheet.

The spreadsheet does have some columns that do some arithmetic
calculations. I can easily have the perl script perform those
calculations itself, so that all the values in the text file are "hard
coded". But the problem is that there will be a few errors in my data
stream that the perl script won't catch, and I will need to go back
into the spreadsheet and correct those by hand. If the values were
hard coded, then I may have to re-perform the calculations for several
columns. But if somehow I could have the perl script output a formula
into the text file instead of an actual value, then I would only need
to go back into the spreadsheet and change one column if there was an
error.

But I have no idea how to do that, or if it is even possible. For
example, one of the cells in one of the rows in my spreadsheet right
now uses the formula:
=IF(G1883="Long",AE1883/R1883,AE1883/AD1883)

But if I am trying to create a tab delimited data file, I won't know
what row number each set of data will occupy, so I won't be able to
output any formulas. Is there a way for me to do this?
 
I think I would create the text file without the checks.

Then create a macro that would import the data, do the things I wanted
(formatting, headers, page setup, filtering, sorting, ...) and even adding extra
columns with formulas that I needed.

Option Explicit
Sub Testme01()

Dim myFileName As Variant
Dim LastRow As Long
Dim TabWks As Worksheet

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName

Workbooks.OpenText Filename:=myFileName, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1)

Set TabWks = ActiveSheet

With TabWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("az1:az" & LastRow).Formula _
= "=IF(G1=""Long"",AE1/R1,AE1/AD1)"
.Rows(1).Insert
With .Range("A1").Resize(1, 5)
.Value _
= Array("Header1", _
"header2", _
"header3", _
"header4", _
"header5")
.Font.Bold = True
End With
.UsedRange.AutoFilter
.UsedRange.Columns.AutoFit
.Parent.SaveAs _
Filename:="C:\somefile_" & Format(Now, "yyyymmdd_hhmmss") & ".xls",
_
FileFormat:=xlWorkbookNormal
End With

End Sub
 
ps. You could record a macro to make sure that the data was parsed correctly
when it was brought into excel. That's what I did with my test data.
 
Back
Top