Using Excel with VB

[sbain]

What is the method to open an Excel spreadsheet and read in
the information within the cells. I don't want to open the
spreadsheet just read the data and manipulate the data as I
see fit within the VB application.

[JHausmann]

Problem is the spreadsheet file has a bunch of junk in it that Excel understands and isn't textual. So, unless you have the format of the file, you must rely on Excel to do the translating for you...

[Clunietp]

Do you want to manipulate the Excel sheet via Automation (Excel Object Model) or do you want to read the excel sheet via ADO?

[sbain]

This is an example of all I can find but when run it doesn't do anything at all.
I read, somewhere and I can't remember, that when you open an external database you specify an option so that the sheet
does not open visibly.
I've not tried ADO with the sheet but just completed a project binding a db via ADO to a form using Access DB.
This is not what I want to achieve as it shouldn't be
opened to be shown. Like a text file and using the OPEN
command and reading the file's data but not seeing the file.

Public Sub LinkExcelSheet()
Dim dbsJet As Database
Dim rstSales As Recordset
Dim tdfExcelSheet As TableDef
Set dbsJet = OpenDatabase("C:\Samp.mdb")
' Create a TableDef object.
Set tdfExcelSheet = dbsJet.CreateTableDef _
("Linked Excel Worksheet")
' Set connection information.
tdfExcelSheet.Connect = "Excel 5.0;" _
& "Database=C:\Sales\Q1Sales.xls"
tdfExcelSheet.SourceTableName = "January Sales"
' Append the TableDef object to create a link.
dbsJet.TableDefs.Append tdfExcelSheet
' Open a Recordset object on the Microsoft Excel
' worksheet
Set rstSales = dbsJet.OpenRecordset _
("Linked Excel Worksheet")
End Sub

[Clunietp]

manipulate Excel via ADO

uses ADO 2.1 or higher (for Jet 4 provider)

    Dim cn As New Connection
    Dim rs As New Recordset
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.xls;Extended Properties=Excel 8.0;"
    
    'excel sheet name here
    'must enclose in brackets, sheet name followed by a dollar sign
    rs.Open "Select * from [Sheet1$]", cn, adOpenKeyset, adLockOptimistic
    
    'see value
    MsgBox rs.Fields(0).Value
    
    'change it
    rs.Fields(0).Value = rs.Fields(0).Value & "3"
    
    'update
    rs.Update
    
    'verify change
    MsgBox rs.Fields(0).Value

    'cleanup....

[quadoc]

Cluniept, I tried your code with my excel database which has 4 columns. The first column contains text, the other columns contain numeric numbers. The rs.Fields().Value for all other columns show nulls instead of the actual numeric values. Could you tell me why it's doing that?

The statement MsgBox rs.Fields(2).Value show null instead of numeric value. But rs.Fields(0).Value shows the correct text.

[Clunietp]

You know, I had the same exact problem on another project where I had to do this, the value would come up as NULL when there was a value there. This is a bug with the driver

I believe we made the column a text column instead of a numeric and that fixed it...

If this doesn't fix it, let me know and I'll do some further research on how I fixed it....


Tom

[quadoc]

Cluniept, Do you mean to save the column as text in the Excel database? How do you format the column to be text in Excel? Thanks!

[VBJ]

Right click the cell in Excell and select Format Cells. Then
select Text from the listbox on the left.

Regards: VBJ

[JHausmann]

Do what VBj wrote as a macro, then cut-n-paste to your code, if you do it frequently.

[quadoc]

Can I format the whole column instead of one cell?
Thanks!

[quadoc]

Well, I made the columns to be text and it still shows nulls in VB. Does anyone know how to solve this? Thanks!

[JHausmann]

You won't experience the problem if you open an instance of an excel application and manipulate the cells directly. You will have to have excel on any machine that runs your app, if you choose to take this approach.

[quadoc]

The problem is some machine don't have Access loaded. Any other suggestions? Thanks!

[JHausmann]

In access help, look up "TransferSpreadsheet Action". You can use this functionality to bring the data into an actual Access table.