Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need the original digit format with zeroes untrimmed
Hello,
My query is about how to retain an 8 digit format while copying data values from one file to another. I have a dbf file with one column containing 8 digit indexes. When I copy some of those into an excelworksheet I get some of the indexes that start with the zero trimmed, eg. 7063004 instead of 07063004. Now, it is important that the indexes I copy are displayed and operated on as 8-digit values (including the initial zeroes). How can I do that? My macro does the following instruction Dim IndeksVal As Long ' (I have also tried String) - this is a temporary value '[....] IndeksVal = DbfData.Worksheets(1).Cells(oCell.Row, 1).Value Application.ThisWorkbook.Worksheets(1).Cells(posco unter, INDX).Value = IndeksVal and the following direct assignent does not work either: '[....] Application.ThisWorkbook.Worksheets(1).Cells(posco unter, INDX).Value = DbfData.Worksheets(1).Cells(oCell.Row, 1).Value Other values that do not start with the zero, get copied "correctly". Alvaro E. Gonzales |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need the original digit format with zeroes untrimmed
Hi
try setting the format of these cell to 'Text' before copying the values. Also try to use the .formula property "alfonso gonzales" wrote: Hello, My query is about how to retain an 8 digit format while copying data values from one file to another. I have a dbf file with one column containing 8 digit indexes. When I copy some of those into an excelworksheet I get some of the indexes that start with the zero trimmed, eg. 7063004 instead of 07063004. Now, it is important that the indexes I copy are displayed and operated on as 8-digit values (including the initial zeroes). How can I do that? My macro does the following instruction Dim IndeksVal As Long ' (I have also tried String) - this is a temporary value '[....] IndeksVal = DbfData.Worksheets(1).Cells(oCell.Row, 1).Value Application.ThisWorkbook.Worksheets(1).Cells(posco unter, INDX).Value = IndeksVal and the following direct assignent does not work either: '[....] Application.ThisWorkbook.Worksheets(1).Cells(posco unter, INDX).Value = DbfData.Worksheets(1).Cells(oCell.Row, 1).Value Other values that do not start with the zero, get copied "correctly". Alvaro E. Gonzales |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need the original digit format with zeroes untrimmed
On Mon, 25 Oct 2004 17:27:22 +0200, "alfonso gonzales"
wrote: Hello, My query is about how to retain an 8 digit format while copying data values from one file to another. I have a dbf file with one column containing 8 digit indexes. When I copy some of those into an excelworksheet I get some of the indexes that start with the zero trimmed, eg. 7063004 instead of 07063004. Now, it is important that the indexes I copy are displayed and operated on as 8-digit values (including the initial zeroes). How can I do that? In order to ensure the leading zeros, I believe you need to treat the value as a properly formatted string, or format the cell in which the index is displayed as "00000000" For example: dim Indx as String Indx = Format(num,"00000000") --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need the original digit format with zeroes untrimmed
I omitted to mention an essential fact: the problem with formatting these
cells to "Text" before copying the values is that I open the source file exclusively for reading, and must not introduce anychanges: the file is used by another program. Użytkownik "Frank Kabel" napisał w wiadomości ... Hi try setting the format of these cell to 'Text' before copying the values. Also try to use the .formula property "alfonso gonzales" wrote: Hello, My query is about how to retain an 8 digit format while copying data values from one file to another. I have a dbf file with one column containing 8 digit indexes. When I copy some of those into an excelworksheet I get some of the indexes that start with the zero trimmed, eg. 7063004 instead of 07063004. Now, it is important that the indexes I copy are displayed and operated on as 8-digit values (including the initial zeroes). How can I do that? My macro does the following instruction Dim IndeksVal As Long ' (I have also tried String) - this is a temporary value '[....] IndeksVal = DbfData.Worksheets(1).Cells(oCell.Row, 1).Value Application.ThisWorkbook.Worksheets(1).Cells(posco unter, INDX).Value = IndeksVal and the following direct assignent does not work either: '[....] Application.ThisWorkbook.Worksheets(1).Cells(posco unter, INDX).Value = DbfData.Worksheets(1).Cells(oCell.Row, 1).Value Other values that do not start with the zero, get copied "correctly". Alvaro E. Gonzales |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need the original digit format with zeroes untrimmed
OK, I see it works when I format the target cells, so it is fine for me,
thank you. Użytkownik "Frank Kabel" napisał w wiadomości ... Hi try setting the format of these cell to 'Text' before copying the values. Also try to use the .formula property "alfonso gonzales" wrote: Hello, My query is about how to retain an 8 digit format while copying data values from one file to another. I have a dbf file with one column containing 8 digit indexes. When I copy some of those into an excelworksheet I get some of the indexes that start with the zero trimmed, eg. 7063004 instead of 07063004. Now, it is important that the indexes I copy are displayed and operated on as 8-digit values (including the initial zeroes). How can I do that? My macro does the following instruction Dim IndeksVal As Long ' (I have also tried String) - this is a temporary value '[....] IndeksVal = DbfData.Worksheets(1).Cells(oCell.Row, 1).Value Application.ThisWorkbook.Worksheets(1).Cells(posco unter, INDX).Value = IndeksVal and the following direct assignent does not work either: '[....] Application.ThisWorkbook.Worksheets(1).Cells(posco unter, INDX).Value = DbfData.Worksheets(1).Cells(oCell.Row, 1).Value Other values that do not start with the zero, get copied "correctly". Alvaro E. Gonzales |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional format on first & last digit on 3 digit cell data | New Users to Excel | |||
Format cells in web query (leading zeroes) | Excel Discussion (Misc queries) | |||
Format 2 digit year to 4 digit | Excel Discussion (Misc queries) | |||
how to keep leading zeroes when saving in xls in csv format | Excel Discussion (Misc queries) | |||
single quote 10-digit number that has leading zeroes & then conca. | Excel Worksheet Functions |