I use VB6.0 and Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

diaExcel - 21 Dec 2007 14:44 GMT

I want to put the data from an Excel file in an Access database using VB6.0
(ADO), how can I do that?

Thank you.

Reply to this Message

Alex Dybenko - 23 Dec 2007 08:04 GMT

Hi,
here how you can automate excel
http://www.mvps.org/access/modules/mdl0006.htm

then you can use DAO to put data into appropriate tables

Signature

Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

>I want to put the data from an Excel file in an Access database using VB6.0
> (ADO), how can I do that?
>
> Thank you.

Reply to this Message

Jamie Collins - 24 Dec 2007 11:10 GMT

On Dec 23, 8:04 am, "Alex Dybenko"
<alex...@PLEASE.cemi.NO.rssi.SPAM.ru>

> >I want to put the data from an Excel file in an Access database using VB6.0
> > (ADO), how can I do that?
>
> here how you can automate excelhttp://www.mvps.org/access/modules/mdl0006.htm
>
> then you can use DAO to put data into appropriate tables

Which is worse: the OP posting an ADO question in the DAO group or
posting an DAO reply to an ADO question? ;-)

Jamie.

--

Reply to this Message

diaExcel - 24 Dec 2007 17:13 GMT

Hi,

I want to import the Excel file in Access database using ADO in VB6.0, can I
do that and how?

Thank you.

"Alex Dybenko" a scris:

> Hi,
> here how you can automate excel
[quoted text clipped - 6 lines]
> >
> > Thank you.

Reply to this Message

Alex Dybenko - 24 Dec 2007 20:01 GMT

Well, no much difference DAO or ADO in this case, get a reference to excel,
open worksheet, open recordset to put data to, loop through required cells
and add records to recordset

Signature

Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

> Hi,
>
[quoted text clipped - 17 lines]
>> >
>> > Thank you.

Reply to this Message

RoyVidar - 25 Dec 2007 13:49 GMT

> Hi,
>
> I want to import the Excel file in Access database using ADO in VB6.0, can I
> do that and how?
>
> Thank you.

You can connect to the Excel file through ADO to retrieve the
information, given that the information is in some kind of order.
Here's some results of a short web search

http://www.ozgrid.com/forum/showthread.php?t=17158
http://support.microsoft.com/kb/326548/en-us
http://support.microsoft.com/kb/262537/en-us

Let's take it a step further. Say you've got the file xlsfile.xls
somewhere, where the headers are stuffed at the top of the sheet (sheet
named xlsfile, too), I think you should be able to do something like
this:

Dim cn As ADODB.Connection
Dim RecsAffected As Long
Dim SQLString As String

Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\<path and name of>.xls;" & _
"Extended Properties='Excel 8.0;HDR=Yes'"
cn.Open
SQLString = "INSERT INTO TargetTable In " & _
"'c:\<path and name of>.mdb' " & _
"SELECT * FROM [xlsfile]"
cn.Execute SQLString, RecsAffected, adCmdText + adExecuteNoRecords
Debug.Print RecsAffected

I think you can also be a bit selective with ranges, say

...SELECT * FROM [xlsfile$A1:X120]"

xlsfile here represenging sheet name.

Signature

Roy-Vidar

Reply to this Message

diaExcel - 27 Dec 2007 14:47 GMT

Thank you so much!!!
I have another question....
in VB6.0 in a form I have a DataGrid which is populated with data from
Access, but now I want to add all rows from this datagrid in a different
table in Access, how can I do that, because I've seen that I have to add row
by row and I don't want to in that way, I want that all the rows in a single
step to be added...

Thank you

"RoyVidar" a scris:

> > Hi,
> >
[quoted text clipped - 37 lines]
>
> xlsfile here represenging sheet name.

Reply to this Message

louiss - 29 Dec 2007 21:40 GMT

answer to your second question
i do it this way:
make a database in acces with the exact number of rows and form as you need
it
than select all the data you want to replace (use ctrl C)
go to the empty database and select all (left up) and do ctrl V
i use this methode to place excell data into acess database, it works perfekt
succes

>Thank you so much!!!
>I have another question....
[quoted text clipped - 13 lines]
>>
>> xlsfile here represenging sheet name.

Reply to this Message

RoyVidar - 30 Dec 2007 11:32 GMT

on 27.12.2007, diaExcel supposed :

> Thank you so much!!!
> I have another question....
[quoted text clipped - 5 lines]
>
> Thank you

[snipped stuff related to previous question]

Dear diaExcel,

though there probably are poeple frequenting this NG that are familiar
with objects of classic VB, I think you probably have better chances
of getting a reply in an NG dedicated to classic VB.

I don't know anything about the control you mention, as it doesn't
exist
in the product for which this NG is dedicated (Ms Access), but, say
you've populated it trough a select (apply WHERE clause as appropriate)

SELECT col1, col2, ...colN FROM SomeTable

you should be able to do something like (air code)

INSERT INTO NewTable
SELECT col1, col2, ...colN FROM SomeTable

- using the first select.

Or, to create a new table, something like

SELECT col1, col2, ...colN
INTO NewTable
FROM SomeTable

You should be able to fire some of this off on an ADO connection to
the db.

But adding redundant data etc, begs the question whether you've chosen
the most optimal design.

Signature

Roy-Vidar

Reply to this Message

louiss - 29 Dec 2007 21:48 GMT

don't use vb 6 for this
make an empty acces database with the exact number of rows as you have in
excel
select all the data (in excel) you want to move (ctrl+C)
go to acces database en select all (upper left corner) and do ctrl+V
i use this methode on an database with 15.000 ithems on 29 rows - perfect
succes

>I want to put the data from an Excel file in an Access database using VB6.0
>(ADO), how can I do that?
>
>Thank you.

Reply to this Message