Updating Excel Data Type / Value Before Import

[Ben]

I'm attempting to import data from an Excel 2002 spreadsheet into Access
2002 (using VBA and an Access form). There are a few columns within the
spreadsheet that could contain no data, a single number or a series of
numbers seperated by a comma. I have written a function to split this data
as appropriate that works OK when the Excel cell data has an ' at the start
(i.e. setting the cell data to text). The problem is that the ' will be not
neccessarily be there during real use.

My plan is to run a function that updates the Excel spreadsheet before
importing, something along the lines of,

MsgBox VarType(xlRS.Fields("F17"))

If Not Len(Nz(xlRS.Fields("F17"), "")) = 0 Then
xlRS.Fields("F17") = "'" & Nz(xlRS.Fields("F17"), "")
End If

The Excel datatype of the cells is 'General', but the VarType function
reports Double as the data type. This means that when I try to update the
cell contents I receive a Type Mismatch error. Is there anyway to
change/update the data type Excel will expect so I can update the cell
contents? If not any suggestions on a better way to import the data?

Thanks,
Ben

 

[John Nurick]

Hi Ben,

The real problem is that Excel doesn't have a concept of data types in
its cells. The "General" you mention is a cell format and only
influences how different kinds of data are displayed in the cell. Access
just looks at the first few cells in each column, guesses a data type
from that, and creates a field accordingly

Something like this will prepend an apostrophe to the contents of each
cell and force it to be imported as a string:

Dim R As Excel.Range
Dim C As Excel.Range

Set R = [the range you're importing]
For Each C In R.Cells
C.Formula = "'" & C.Formula
Next

At least in Excel 2002, it also seems possible to force Excel to treat a
value as a string rather than a number by appending an empty string:

C.Formula = C.Formula & ""

 

[Ben]

Thanks for the reply John. I'm now having trouble
connecting to the Excel file. I have tried various
methods that I have found by searching the newsgroups, but
I always receive the same error - 1004, "Cannot access
<filename>".

I have included the reference to the Microsoft Excel 10.0
Object Library.

Me.txtFileLocation is a text box that contains the full
path to the Excel file (via a File Open dialog box) and
works correctly for a function that is called after the
update function I am trying to write below (i.e. the value
in the box is correct).

Code:

Dim xlBook as Excel.Workbook
Dim R as Excel.Range
Dim C As Excel.Range

Set xlBook = GetObject(Me.txtFileLocation)

Set R = xlBook.WorkSheets("benef monitoring").Range
("Q10:Q32")

For Each C In R.Cells
C.Formula = "'" & C.Formula
Next

Set xlBook = NOthing

Thanks in advance,
Ben

 

[Ken Snell]

You need to get the EXCEL application first; the following code assumes that
EXCEL is not running when your code runs:


Dim xlsApp As Object
Dim xlBook as Object
Dim R as Object
Dim C As Object

Set xlsApp = CreateObject("Excel.Application")
Set xlBook = xlsApp.Workbooks.Open(Me.txtFileLocation)
Set R = xlBook.WorkSheets("benef monitoring").Range("Q10:Q32")

For Each C In R.Cells
C.Formula = "'" & C.Formula
Next C

Set R = Nothing
xlBook.Close True
Set xlBook = Nothing
xlsApp.Quit
Set xlsApp = Nothing


--
Ken Snell
<MS ACCESS MVP>

Thanks for the reply John. I'm now having trouble
connecting to the Excel file. I have tried various
methods that I have found by searching the newsgroups, but
I always receive the same error - 1004, "Cannot access
<filename>".

I have included the reference to the Microsoft Excel 10.0
Object Library.

Me.txtFileLocation is a text box that contains the full
path to the Excel file (via a File Open dialog box) and
works correctly for a function that is called after the
update function I am trying to write below (i.e. the value
in the box is correct).

Code:

Dim xlBook as Excel.Workbook
Dim R as Excel.Range
Dim C As Excel.Range

Set xlBook = GetObject(Me.txtFileLocation)

Set R = xlBook.WorkSheets("benef monitoring").Range
("Q10:Q32")

For Each C In R.Cells
C.Formula = "'" & C.Formula
Next

Set xlBook = NOthing

Thanks in advance,
Ben

-----Original Message-----
The real problem is that Excel doesn't have a concept of data types in
its cells. The "General" you mention is a cell format and only
influences how different kinds of data are displayed in the cell. Access
just looks at the first few cells in each column, guesses a data type
from that, and creates a field accordingly

Something like this will prepend an apostrophe to the contents of each
cell and force it to be imported as a string:

Dim R As Excel.Range
Dim C As Excel.Range

Set R = [the range you're importing]
For Each C In R.Cells
C.Formula = "'" & C.Formula
Next

 

[Ben]

Thanks Ken.. I also realised that I was opening up an ADO connection to the
EXCEL file and then trying to open the file in EXCEL.

Thanks again,
Ben