[RESOLVED] How to have VB6 code modify an EXCEL worksheet?

[camoore]

Hitherto I have always used the ACCESS database, linking to a table through an ADODC control and then linking to that a datagrid. Then I have bound text boxes to the ACCESS table fields. These enable those fields to be read into the program and allow the program to modify the ACCESS database fields back in return. So far so good, and always a reliable procedure.

Now I need to do the same thing with an EXCEL file. Using MS article 257819 plus lot of patient experimentation, I have succeeded in linking an ADODC to the EXCEL file (using Jet 4.0) and linking that ADODC to a datagrid and to a series of databound text boxes. The vital thing was to find out what connection string to use for the ADODC control.

Now two problems arise :

1. The first line of the EXCEL sheet gets "hijacked" by the datagrid and appears as field headers. I do not want this to happen. I wish the fields to be labelled F1, F2, F3 etc. and the first line of the EXCEL sheet to be the first line of the datagrid.

2. The databound text boxes accept changed data, and upon adodc.recordset.movenext those changes are reflected in the datagrid. Thus far exactly as it works with an ACCESS table. However those changes are NOT reflected back into the EXCEL file, and I have failed to find how to achieve this. With an ACCESS table, the source ACCESS database would be updated.

The objectives are :

a. To be able to change the very first row (and others) of the source EXCEL worksheet to different values by use of VB6 at runtime and

b. To be able in all respects to use an EXCEL worksheet with databound text boxes as is possible with an ACCESS table.

I seem to have got this thing very close to working, but not quite.

Hence, comments and suggestions would be much appreciated.

camoore

Wales, UK

[dee-u]

First things first, using a databound control like adodc is a very bad thing in VB6, it is not reliable and tend to give lots of problems.

There should be threads in the FAQ and codebank section for manipulating excel files, have a search.

[camoore]

Thank you for the reply dee-u.

I am aware that many members do not like the databound control approach, but it is what I have brought myself up on in VB6 with ACCESS databases of not huge size, and thus far it has never let me down.

I have made some progress with my current problems. Thus far the solution seems to lie in writing the correct connection string to link the ADODC control to the EXCEL database. I have now achieved half of what I was initially aiming for - a datagrid which fully displays the full content, regardless of column classification (String vv Numeric) of an EXCEL sheet with every field linked to a dedicated text box and thence into my VB program. I have got over the "hijacked" first line issue.

All I now want to achieve is the ability to have the contents of my datagrid written back into the source EXCEL file. If I can do this, this present problem will be mostly solved.

Concerning your suggestion about a codebank search etc, and FAQs, I did this at some length before making the post. I could not find anything directly relevant to what I am trying to do.

Surely it can not be very difficult to transfer a modified datagrid back into an EXCEL file?


MS Article 257819 deals with the matter at some length, but as yet has not solved all of my question.


camoore

Wales, UK

[klen_]

MS Article 257819 ...

support.microsoft.com/kb/194124
support.microsoft.com/kb/247412

[dee-u]

Using IMEX=2 would persist the changes, here is what I've tried and works.

Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\PIS\PISClient\02082007.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"";"
Adodc1.RecordSource = "SELECT * FROM [02082007$]"
Adodc1.Refresh

[camoore]

Thank you klen and dee-u.

The problem with our EXCEL datafile is that the columns contain a mixture of numeric and text entries. What I want to do is to force recognition of all columns as Text. Hence I wish to write some "dummy" text entries into the first few lines. This will, I hope, cause subsequent processes to classify all columns as Text. Otherwise Text cells get treated as nulls if their columns are classified as numeric.

I can not get the data retention to work using Imex=2. If I change the value of a cell in the Adodc-linked datagrid, move the recordset forward and then back it appears to be registered in the datagrid, but the EXCEL file is not altered.

Also I have to use Imex=1 in order to be able to read Text in columns which have been classified as numeric.

Is there another approach to this? I have read of the "cell by cell" method of changing the content of an EXCEL file. Suppose my file is C:\Folder\File.xls and I wish to make cell C4 of Sheet 1 contain the text "Cell C4". Can you please show me an illustration of code how to do this? I have tried to do this from the various MS articles, but thus far without success.


camoore

Wales, UK

[si_the_geek]

To work with particular cells you cannot treat the file as a database, you need to use automation.

See my Excel tutorial (link in my signature) for an explanation of how to do it.

[camoore]

Thank you si the geek. I have been doing just that, with a degree of success.

Thus far I can open an Excel file, change the value of cells and save it as another file. This will suit the purpose OK, but at the moment once the program has finsihed I am getting a message from Excel that the file is still open and now available for editing. I can't seem to get it to close down completely.

It is interesting that dee-u seems to have got data change retention working on an Excel file. I could not make that work with Imex=2 or anything else I tried.

Here is a specimen of my code thus far :

    Dim oXLApp As Excel.Application
    
    Dim oXLBook As Excel.Workbook
    
    Dim oXLSheet As Excel.Worksheet
    
    
    
    Set oXLApp = New Excel.Application
    
    Set oXLBook = oXLApp.Workbooks.Open("C:\RAILPROJECT\7.125.110.xls")
    
    Set oXLSheet = oXLBook.Worksheets(1)
    
    oXLApp.Visible = False  '(do not want operator to see it)

    
    oXLSheet.Cells(3, 2).Value = "Field2"
    oXLSheet.Cells(3, 5).Value = "Field5"

    'etc. etc. Here I specify the values I want for various cells, then .....
   

    
    Set oXLSheet = Nothing
    
    oXLBook.SaveAs "C:\RAILPROJECT\7.125.110A.xls"
    
    Set oXLBook = Nothing
    
    Set oXLApp = Nothing
    
    'now display the modified file at a datagrid, which is linked to Adodc1
        
    Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\RAILPROJECT\7.125.110A.xls;Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"

    Adodc1.RecordSource = "[SHEET1$]"

    Adodc1.Refresh


    'but how now to have both Excel files (110 and 110A) close down? Seems as if I need a close statement for the two files?

camoore

Wales, UK

[si_the_geek]

When you post code please put it inside code tags (which I have added to your last post) so it is displayed in a more readable way - either using the Code/VBCode buttons in the post editor screen (or at the top of the Quick Reply box), or by putting them in manually, like this: [code] code here [/code]


As to the issue, it doesn't close down at all, because you aren't telling it to (the closest you do is just a SaveAs). See the relevant part of the tutorial (post #5 I think) to see the valid ways to do it.

[camoore]

Thank you si the geek.

I must compliment you on the quality of that tutorial you have written, which I had been reading. I had missed the need for the oXLApp.quit line. Having added this to the closing section of my code, it starts to behave OK.

'now close down the EXCEL package(s) open

    oXLBook.Close SaveChanges:=False
    
    Set oXLSheet = Nothing
    
    Set oXLBook = Nothing
    
    oXLApp.Quit
    
    Set oXLApp = Nothing
    
    GoTo Line1000                         'and exit routine

May I please ask a supplementary question about automation. Is it possible similarly to automate from VB an ACCESS database?

My wishes are : Within an established ACCESS database file, containing several Tables :

1. To make a copy of Table abc and to add it to the .mdb file but named as Table efg

2. To be able to delete Table abc from the .mdb file altogether.

Any information or pointer to another tutorial would be appreciated.


camoore

Wales, UK

[si_the_geek]

You can use automation with Access (see the Office Development FAQs for examples), and I'm pretty sure you can use it to do those things... but it will probably easier and quicker (as well as not needing Access to be installed) to do them using standard database code.

In this case you want to use Create Table and Drop Table. If you want to copy the data across too, use an Insert statement (with a Select statement instead of a Values clause). There is information about them via the "SQL" section of the DB FAQs.

[camoore]

Thanks again Si the geek.

This topic (Access automation) is beyond my sphere of knowledge. I will look at the FAQs as you advise. Any further suggestions from forum members as to how to "control" a .mdb database from within a VB program welcomed.

camoore

Wales, UK

[camoore]

Having done my best to read and understand the references, plus various MS Articles on the topic (eg,. kb/150418 and kb/108146) I have not been able to get any code to work for me.

Since this thread started on the topic of Excel and, thanks to your help, that part has been resolved I will mark it as Resolved and start a new thread about the Access query.

Thank you all,

camoore

Wales, UK