I need the original digit format with zeroes untrimmed

A

alfonso gonzales

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(poscounter, INDX).Value =
IndeksVal

and the following direct assignent does not work either:
'[....]

Application.ThisWorkbook.Worksheets(1).Cells(poscounter, 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
 
G

Guest

Hi
try setting the format of these cell to 'Text' before copying the values.

Also try to use the .formula property
 
R

Ron Rosenfeld

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
 
A

alfonso gonzales

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 said:
Hi
try setting the format of these cell to 'Text' before copying the values.

Also try to use the .formula property

alfonso gonzales said:
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(poscounter, INDX).Value
=
IndeksVal

and the following direct assignent does not work either:
'[....]

Application.ThisWorkbook.Worksheets(1).Cells(poscounter, 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
 
A

alfonso gonzales

OK, I see it works when I format the target cells, so it is fine for me,
thank you.
U¿ytkownik "Frank Kabel said:
Hi
try setting the format of these cell to 'Text' before copying the values.

Also try to use the .formula property

alfonso gonzales said:
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(poscounter, INDX).Value
=
IndeksVal

and the following direct assignent does not work either:
'[....]

Application.ThisWorkbook.Worksheets(1).Cells(poscounter, 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top