There are at least two ways to get the data from Excel; one is to open
Excel from your program and command it to open the worksheet and extract
the data. The other is to treat the sheet as a database and retrieve data
from it directly using ActiveX Data Objects. The latter method has a few
drawbacks but is faster and more elegant.
Your Excel sheet looks a lot like a database. You could name that data
range in Excel as a named range; suppose you call it "HookData". Then you
could retrieve the data from it using something like this:
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim strSQL As String
Set oConn = New ADODB.Connection
Set oRS = New ADODB.Recordset
' Watch out for line wrap ... the following is one line!
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Data\Hook.xls;Extended properties=Excel 8.0;Persist Security
Info=False"
' End "oConn.Open ... " line.
' Note that the "Excel 8.0" is the _file_ _format_ version, not
' the Excel version.
oConn.Open
' More line wrap coming up!
strSQL = "Select Parts_no, Parts, Size, Qty, Remarks from HookData Where
Prjname = 'NCC' and Detail = 'A14'"
oRS.Open strSQL, oConn
.
.
.
oRS = Nothing
oCmd = Nothing
oConn.Close
oConn = Nothing
To make this work, in the VBA editor you need to pick "Tools" "References",
scroll down to Microsoft ActtiveX Data Objects Library ..." and put a check
mark by the highest-numbered version listed.
The query listed would retrieve the data from the last four rows of your
table. In your real program you would need to construct the query by
concatenating (the & operator) the constant parts with the variable parts.
Note the use of single-quotes to surround strings inside the query.
In addition to any replies you might receive or already received, you may
find more information or responses by posting future connectivity related
questions in the following discussion group:
Web browser: <>
Newsreader: <>
--
jrf
Autodesk Discussion Group Facilitator
Please do not email questions unless you wish to hire my services
On Fri, 8 Dec 2006 02:32:50 +0000, santhana wrote:
> Dear Experters,
>
> I am New to vba & Autocad.
> I want to add or modify a data to autocad Table.
> The input data are in the form of excel file.
> According to Excel File data,I have to open Autocad Drawing and update
the Text Data only in the
> Table.(Not an Attribute data)
>
> For example 100 drawing data is available in excel sheet.i have to open
first drawing and update the
> data ,save drawing and close.then open next drawing,according to excel
file data and update the
> data,save and clos
> e drawing.This routine can be done the data is available in excel sheet.
>
> I have to give sheet number also for ex: 1 of 100,2 of 100 etc...,
>
> For Example - Please see the attachement.
> 1.Excel Sheet Data.
>
> Thanks in Advance,
> Santhana
