Discussion
Can anyone help?
I am trying to insert a recordset, using ADO, into an excel sheet.
The problem I am having is that everytime I try and inset the record it goes in as adVarWChar which is unset and so defaults to a string.
Is there any way of setting the type of value in the SQL statement?
Cheers
I am trying to insert a recordset, using ADO, into an excel sheet.
The problem I am having is that everytime I try and inset the record it goes in as adVarWChar which is unset and so defaults to a string.
Is there any way of setting the type of value in the SQL statement?
Cheers
I think ive found the problem! But I dont like the answer!!
I am basically forced to use Excel as a database. So im using an ADO connection to write/read to the Excel workbook.
The problem is is that the first record you add adopts the format of a string across all fields, despite the fact the formatting is set (not using general)
The reason being is because the headers are automatically set to string, and so the addnew is copying down the formatting of the previous line, ie the header, and so everything is a string.
I might try getting away without a header, although at the moment, ive had to load a dummy line in it, which is not a very slick way of doing it!
I was hoping that there is a way of forcing the formatting of the insert in the SQL statement, but I dont think there is?
I am basically forced to use Excel as a database. So im using an ADO connection to write/read to the Excel workbook.
The problem is is that the first record you add adopts the format of a string across all fields, despite the fact the formatting is set (not using general)
The reason being is because the headers are automatically set to string, and so the addnew is copying down the formatting of the previous line, ie the header, and so everything is a string.
I might try getting away without a header, although at the moment, ive had to load a dummy line in it, which is not a very slick way of doing it!
I was hoping that there is a way of forcing the formatting of the insert in the SQL statement, but I dont think there is?
canam-tt said:
I was hoping that there is a way of forcing the formatting of the insert in the SQL statement, but I dont think there is?
I'm thinking you hit the nail on the head here...
My knowledge of SQL is pretty minimal but I've never come across anything that resembles this.
Best bet might be to migrate to Access and then use the Access Database to create your Excel Workbook. Otherwise you might be able to create a Macro that formats the sheet columns when the sheet is loaded?
Another way to do it, is to write a file in whatever format you want. The next stage is to write code in C or VB or whatever, that reads the file you created. If you compile this code up as a DLL, you can easily call the DLL from Excel to read the file in your own format.
Another possibility, is to produce a DLL that, interfaces directly with whatever environment you're currently in.
Edited to add;
I'm not actually clear if you're actually writing the ActiveX code. If you are, you can use the DLL you build, and call that from Excel. You will need to use either DDX or an microsoft memory filemapping to be able pass data between the two processes, assuming your activex object is in a different app from the Excel target. Obviously you cant communicate between two processes without using DDX or a filemapping because of the user mode protection model. It does mean though that all of the interface code could be in a single ActiveX DLL. If you use the filemapping, the interface would be pretty direct, so you would see the context switches between the two processes. All you need to do is use a Win32 Mutex to synchronize through the filemapping.
(If you see what I mean)
Further edited to add;
DDX, should read DDE (Dynamic Data Exchange).
Assuming that ADO stands for ActiveX Data Objects.
>> Edited by dilbert on Monday 14th November 17:32
Another possibility, is to produce a DLL that, interfaces directly with whatever environment you're currently in.
Edited to add;
I'm not actually clear if you're actually writing the ActiveX code. If you are, you can use the DLL you build, and call that from Excel. You will need to use either DDX or an microsoft memory filemapping to be able pass data between the two processes, assuming your activex object is in a different app from the Excel target. Obviously you cant communicate between two processes without using DDX or a filemapping because of the user mode protection model. It does mean though that all of the interface code could be in a single ActiveX DLL. If you use the filemapping, the interface would be pretty direct, so you would see the context switches between the two processes. All you need to do is use a Win32 Mutex to synchronize through the filemapping.
(If you see what I mean)
Further edited to add;
DDX, should read DDE (Dynamic Data Exchange).
Assuming that ADO stands for ActiveX Data Objects.
>> Edited by dilbert on Monday 14th November 17:32
so you're probably doing it the right way after all! 
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff