1

I want to get data from a column in excel sheet. In my case it's the 3rd one. I use a for loop in order to scan all the cells and I have the following column :

1,724.87
1,560.00
18,109.61
64.37
4,898.22
1,784.47
43,430.30

When I use the WorkSheet.Cells.Item($x,3).Text function in the loop to get the content of each cell, I get the following :

1,724.87
1,560.00
#######
64.37
4,898.22
1,784.47
#######

The ####### can be seen in the .xls file when I open it in excel. When I expand the width of the column it shows the actual number again. So I assume that it reads it as it is. What can be done in order to get the actual number?

Marked One
  • 294
  • 2
  • 3
  • 13
  • 1
    that's wierd. You probably can solve this issue and many others or You can start using ImportExcel module. – Tomek Jul 09 '18 at 09:19
  • 2
    Can you try `WorkSheet.Cells.Item($x,3).Value`? – Vityata Jul 09 '18 at 09:55
  • `WorkSheet.Cells.Item($x,3).Value` keeps it the same – Marked One Jul 09 '18 at 10:11
  • 1
    Before you read the text from that column, use: `WorkSheet.Cells.Item($x,3).Columns.Autofit()` – SavindraSingh Jul 09 '18 at 11:14
  • Well it does helps. Question is what happens when I won't be able to know if a column has hashtags? There is an option to auto-fit for the whole sheet? – Marked One Jul 09 '18 at 12:51
  • 1
    `WorkSheet.Columns.Autofit` should be for the whole sheet. Just wondering, would it work, if you try `WorkSheet.Cells.Item($x,3).Value2`? – Vityata Jul 09 '18 at 13:08
  • Interesting, seems to be that `WorkSheet.Cells.Item($x,3).Value2` works. Found this thread that makes more sense regarding the difference between the 2 : https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 – Marked One Jul 09 '18 at 13:26

1 Answers1

0

It seems that the issue with WorkSheet.Cells.Item($x,3).Text is that .Text will represent anything shown on the screen in the cell. In my case is ####### To show the actual value of the cell ,should be using WorkSheet.Cells.Item($x,3).Value2. More difference between .Value, .Value2 and .Text can be found in this post : What is the difference between .text, .value, and .value2?

Marked One
  • 294
  • 2
  • 3
  • 13