importing data into a workbook

Greetings

I am attempting to import a simple text file into a workbook to allow 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, it changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11 (Displayed value)

I have my separate cells to copy specific data from each line into its 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 even Custom but it still reverts back.

Any clues?

Cheers
Pb

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson

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.


PizzaBoy wrote:
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

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)






PizzaBoy wrote:
>
> Greetings
>
> I am attempting to import a simple text file into a workbook to allow
> 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, it
> changes from(Q3 cell value) 211604013993 in the cell to 2.11604E+11
> (Displayed value)
>
> I have my separate cells to copy specific data from each line into its
> 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 even
> Custom but it still reverts back.
>
> Any clues?
>
> Cheers
> Pb
>
> --
> PizzaBoy
> ------------------------------------------------------------------------
> PizzaBoy's Profile: http://www.excelforum.com/member.php...o&userid=26536
> View this thread: http://www.excelforum.com/showthread...hreadid=398009

--

Dave Peterson