I use VB6.0 and Access

[diaExcel]

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.

 

[Alex Dybenko]

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

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

 

[Jamie Collins]

On Dec 23, 8:04 am, "Alex Dybenko"

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.

--

 

[diaExcel]

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:

 

[Alex Dybenko]

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

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

 

[RoyVidar]

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.

 

[diaExcel]

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,

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.

 

[louiss]

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....
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:
[quoted text clipped - 37 lines]

xlsfile here represenging sheet name.

 

[louiss]

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

 

[RoyVidar]

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

[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.