"Number stored as text" error when export to excel 1

[mbowler9]

Hello all.

I am having a problem with saving queries to excel. Some of my fields have the "number stored as text" indicator on them in the spreadsheet. The first question I have is, do I need to convert these? The fields will never be used for any calculations, I would just like to remove the green arrows on the spreadsheet. If so, how would I go about this? I have tried "val(field)", but it did not work. The numbers consist of phone numbers (##########), software versions (#.#), single digits (#), memory (####.#), and other similar fields.

FYI, there are 6 fields in each of about 50 reports. I will be exporting these all at one time via a soon to be written script. The reason I mention this is it may make more sense to do this in the VBA script that saves them in excel format.

Thanks in advance.

 

[combo]

Hi,
It is not a real error (you can for instance still perform arithmetic operations on such cells). A number can be presented as text by adding leading apostrophe, by formatting cell as text or as in your case, some export from access.
You can switch off reporting this by green dot. Go Tools>Options>error checking and untick reporting this.
You can use smart tag option to convert text to number.
Finally, still in excel, write 0 in any free cell, copy it, select green-dotted range and go Edit>Paste special. Choose 'values' to paste and mark 'add' as an operation.
In sase of few cells, it is also possible to re-enter each.

When you convert data to number in your query, or field type is numeric, you will get numbers as numbers.

combo

 

[mbowler9]

I have this problem in about 50 queries and thousands of others have access to them once in excel, so I can't really open up all the spreadsheets and manually fix them every time they run. I need to have something that will fix it from code.
I can't set the fields to number either because some of them have values that look like:
5.3
4.2
1440-23


A sample of the code I am using to export them is like this:

DoCmd.TransferSpreadsheet acExport, 8, "qrysample", _
path & "sample query" & current, True

Any other suggestions?

 

[combo]

I doubt if you can to overcome it in access - when you export text field with number - this is the result. However, in excel XP (or in access with OLE automation) you can:

[tt]Sub abc()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If c.Errors.Item(xlNumberAsText).Value = True Then
c = c + 0
End If
Next c
End Sub[/tt]

In case of automation an explicit path to worksheet (instead of ActiveSheet) is preferred.

combo

 

[mbowler9]

So how would I code this if I have 50 of the following code segments in order with different query and spreadsheet names? The spreadsheets are all different sizes and formats.

DoCmd.TransferSpreadsheet acExport, 8, "allmid_REGION_BY_MODELS_ALL_HP", _
path & "allmid REGION BY MODELS ALL HP" & current, True
Call CountIt

DoEvents


I tried simply adding "call abc" before call CountIt, but got an error saying "Object variable or With block variable not set"


Thanks

 

[combo]

Hi,
the code above works in excel - add it to workbook (standard module), open a workbook where you want to convert text to numbers. The sheet you operate should be active. Note that it uses excel XP collection 'Errors', earlier versions do not report this way.

In access you need to use OLE automation. Below the sample code. After exporting data it opens workbook, removes errors and quits. Required reference to excel library. See excel vba help for description of excel objects and its properties.

[tt]Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlWks As Excel.Worksheet
Dim xlRange As Excel.Range
Dim xlWbkName As String

' transfer data
xlWbkName = Path & "allmid REGION BY MODELS ALL HP" & current
DoCmd.TransferSpreadsheet acExport, 8, "allmid_REGION_BY_MODELS_ALL_HP", xlWbkName, True

Call CountIt
' apply automation
' works since excel XP - new 'Errors' collection
' in case of errors add On Error Resume Next
Set xlApp = CreateObject("Excel.Application&quot
Set xlWbk = xlApp.Workbooks.Open(Filename:=xlWbkName)
For Each xlWks In xlWbk.Worksheets
For Each xlRange In xlWks.UsedRange
If xlRange.Errors.Item(xlNumberAsText).Value = True Then
xlRange = xlRange + 0
End If
Next xlWks
Next xlRange
xlWbk.Close SaveChanges:=True
xlApp.Quit

' clear object variables
Set xlRange = Nothing
Set xlWks = Nothing
Set xlWbk = Nothing
Set xlApp = Nothing[/tt]

combo

 

[mbowler9]

You say that this is specific to Excel XP. Does this mean there isn't a way to do this if I use Excel 97?

Thanks.

 

[mbowler9]

I just tried to run this code and the part that is "Next xlWks" generated an error that said "Invalid Next control variable reference"

Any thoughts or suggestions?

Thanks

 

[combo]

Ooops, sorry, there should be reverse order of Nexts:
[tt] Next xlRange
Next xlWks[/tt]

As for excel versions - the green dot appears since excel XP, previous versions haven't this ferature. For VBA users excel XP object model was extended br Errors collection of Error objects.
For excel 97 re-entering should work, Dim x and replace internal loop:
Dim x as Variant
' and the loop:
[tt] For Each xlRange In xlWks.UsedRange
x = .Value
.ClearContents
.Value = x
Next xlRange[/tt]

combo

 

[mbowler9]

The code is definitaly working. I put it on the first of 50 queries in my list and all the "green dots" are gone. The only problem now is that for some reason it is hanging on the "End If" and won't go on to export the other queries.

When I go to open the spreadsheet it warns me that it is already opened. I suppose this would make sense if it is hanging before the "xlWbk.Close" statement.

What would you suggest here. I can't think of any reason why it would hang.

 

[combo]

Hi,
I suggest to treat the code as a starter and analyse it - it does not check many things, for instance if you open workbook with formulas, with this code they will be converted to values. I haven't tested it, what happens in case of not used worksheets (UsedRange!). Maybe 'On Error Resume Next' or 'On Error Resume GoTo ...' are necessary.
If you need to work with unknown office version - a simple check will be useful (If Val(xlApp.Version)>9 Then ...).
In some cases you can't edit cells, for instance for protected worksheet. Maybe access need some time to generate excel file and the code has to wait for it - in that case ask access experts how to do it.

combo

 

[mbowler9]

Thanks for your help. I thought that it was hanging but it was actually taking about 7.5 minutes to fix the errors in each spreadsheet (they are very large). It works perfect.

 

[mbowler9]

I have been using the above code offered by Combo for several months now. It has worked flawlessly, but I'm afraid we have outgrown it. We now have 75 reports, some of them which are 4 and 5 times larger than our previous ones. It takes over 4 hours to run all these reports. I ran the reports without the function that fixes the errors and it takes less than 8 minutes. Obviously this is a very intensive operation.

CPU usage with fixing = 100% (>240 minutes)
CPU usage w/o fixing = 35% (<8 minutes)

Can anyone offer another route to remove these errors from excel?

Thanks

 

[combo]

Hi,
The code is slow due to automation and checking every cell for error. You can transfer a part of code to excel and here clear errors for opened report. It is possible for instance to create an add-in, call macro by keystroke and proceed on opened workbook.
You can also try the code below. It creates empty workbook, copies first cell and adds its contents (0) to each nonempty cell without formulas. Text is not changed. On Error.. statement is due to SpecialCells(..) return error if no cells satisfying condition to return. Let me know if it is faster.
NB, there is VBA forum707, it is more likely to get VBA office application solution there.

[tt]Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook, xlWbkT As Excel.Workbook
Dim xlWks As Excel.Worksheet
Dim xlWbkName As String

' transfer data
xlWbkName = Path & &quot;allmid REGION BY MODELS ALL HP&quot; & current
DoCmd.TransferSpreadsheet acExport, 8, &quot;allmid_REGION_BY_MODELS_ALL_HP&quot;, xlWbkName, True

Call CountIt
' apply automation
' works since excel XP - new 'Errors' collection
' in case of errors add On Error Resume Next
Set xlApp = CreateObject(&quot;Excel.Application&quot
Set xlWbkT =xlApp.Workbooks.Add(xlWBATWorksheet)
xlWbkT.Worksheets(1).[A1].Copy
Set xlWbk = xlApp.Workbooks.Open(Filename:=xlWbkName)
For Each xlWks In xlWbk.Worksheets
On Error Resume Next
xlWks.UsedRange.SpecialCells(xlCellTypeConstants).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
On Error GoTo 0
Next xlWks
xlWbkT.Close
xlWbk.Close SaveChanges:=True
xlApp.Quit
Set xlWbkT=Nothing
Set xlWbk=Nothing
Set xlApp=Nothing[/tt]

And the same cleaning in excel (could be polished, here assumed that active workbook has to be processed):

[tt]Dim xlWbk As Workbook, xlWbkT As Workbook
Dim xlWks As Worksheet

Set xlWbk = ActiveWorkbook
Set xlWbkT = Workbooks.Add(xlWBATWorksheet)
xlWbkT.Worksheets(1).[A1].Copy
For Each xlWks In xlWbk.Worksheets
On Error Resume Next
xlWks.UsedRange.SpecialCells(xlCellTypeConstants).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
On Error GoTo 0
Next xlWks
xlWbkT.Close[/tt]

combo

 

[mbowler9]

Combo,
I tried to give you more stars, but they aren't showing up. You deserve a dozen of them for this. Here are some specs for you. They are outstanding.

Ran one of my larger queries (23cols X 5875rows = 135,125 cells).

Original fixing function = 15 minutes, 17 seconds
Original fixing function w/o checking for errors (just add 0 to every cell) = 9 minutes, 9 seconds.

Your last suggestion = 0 minutes, 36 seconds.

Just because I was curious, I ran the same report without the fixing function and it kicked out the spreadsheet in 32 seconds.

Basically, your function cut my error fixing time down from nearly 15 minutes to 4 seconds. This is for just one report and I have 75 of them!!!

One issue remains, although I can certainly live with it. When the final product is opened, all the cells are selected/highlighted from the paste operation. Is there anything that can put the focus on say, cell A:1 before I close, or somehow take the focus off of the range?

Thanks again.

 

[combo]

Hi,
I'm glad it's so fast. To select cell A1, add row:

[tt] On Error GoTo 0
xlWks.Range(&quot;A1&quot.Select ' line to add
Next xlWks[/tt]

combo