importing data into a workbook

  • Thread starter Thread starter PizzaBoy
  • Start date Start date
P

PizzaBoy

Greetings

I am attempting to import a simple text file into a workbook to allo
it to apply the information to cells and print.

Sub load()
'
' load Macro
'

Sheets("A").Select
Range("Q3:Q53").Select
Selection.ClearContents
ChDir "C:\messages"
Workbooks.OpenText FileName:="C:\messages\55C.txt"
Origin:=xlWindows _

Range("A1:A50").Select
Selection.Copy
Windows("MURTCM.XLS").Activate
Range("Q3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("55C.txt").Activate
ActiveWindow.Close
Sheets("Main Menu").Select
End Sub



Here is a single cell example of what happens;
Once the data (mixed alpha and numerical) is copied to the cells, i
changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+1
(Displayed value)

I have my separate cells to copy specific data from each line into it
own cell.
this is the formula for cell M7;

=IF(Q3="","",MID(Q3,10,3))

The result in cell M7 should be 933 instead its #VALUE!

I have tried to format the Q3:Q53 cell range to reflect Text and eve
Custom but it still reverts back.

Any clues?

Cheers
P
 
Hi,

I had a similar problem. I was working with Delphi to Excel. I
couldn't get it to work in Delphi. I did get it to work in .NET.

Here's what I did:

Excel.Application oXL;
oXL = new Excel.Application();

int[,] ColumnFormattingArray = new int[6,2]
{{1,1},{2,1},{3,1},{4,2},{5,2},{6,2}};
oXL.Workbooks.OpenText("c:\test.txt", Type.Missing, 1,
Excel.XlTextParsingType.xlDelimited,
Excel.XlTextQualifier.xlTextQualifierNone,
Type.Missing, Type.Missing, Type.Missing, true, Type.Missing,
Type.Missing, Type.Missing, ColumnFormattingArray, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oXL.Visible = true;

I needed column 5 to format as text so it wouldn't do the "2.11604E+11"
thing. The {5,2} in the array tells column 5 to format as text when it
dumps to Excel. The {1,1} in the array tells column 1 to format as
general. I had more than 6 columns in my spreadsheet, but I didn't
need any formatting done to them, so I didn't have to include them in
the array. I only had to list up to column 5 since that was the column
I needed to format.
 
Actually, the value in that cell didn't change. But the way it was displayed to
you did.

211604013993
and
2.11604E+11

have the same underlying value. If you change the format from General to Number
(0 decimals), you'll see that.

And when I put 211604013993 in A1 (and saw 2.11604E+11), I got 993 from this
formula: =MID(A1,10,3)

But this formula returns text '993. If you're using that result in another
formula, then I can see where the #value! error comes from--but I don't see it
just from the formulas you posted.

If you want to return a number, you could use: =--mid(a1,10,3)
 
Back
Top