I need the original digit format with zeroes untrimmed

  • Thread starter Thread starter alfonso gonzales
  • Start date Start date
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
 
Hi
try setting the format of these cell to 'Text' before copying the values.

Also try to use the .formula property
 
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?
Click to expand...

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
 
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
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
 
You must log in or register to reply here.

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

Similar Threads

update record in mdb.. 1
leading zero issue when converting to time format 1
vba format question 2
Using column with numbers and leading zeros 2
How to retrieve number of visible digits after decimal point in VBA 5
filling 2D array with no-zero values 3
Update this macro to make it generate a counter as text 7
Conditional Formatting with VBA 4

Back
Top