Shamil Salakhetdinov
shamil at users.mns.ru
Mon Jan 23 18:03:20 CST 2006
John,
Have a look here is a generic code to get data to ms excel worksheet
from MS Access database and to save data to MS Access db from ms excel
worksheet using disconnected ADO recordsets. This sample uses arrays
because an MS Excel range value is in fact a variant array. It shouldn't
be used with very large source recordsets but as far as I understood you
will not have such recordsets...
Private Const mcstrMdbFullPath As String = _
"<Your sample mdb fullpath here>"
Private Const mcstrSql As String = _
"select [CategoryID],[CategoryName],[Description] from [Categories]"
Private Const mcstrDelSql1 As String = _
"delete * from [Categories1]"
Private Const mcstrSql1 As String = _
"select [CategoryID],[CategoryName],[Description] from [Categories1]"
'Note: Categories1.CategoryId is assumed to be long integer not
autonumber
Public Sub Tests()
' sample code for MS Excel
Dim xlApp As Excel.Application
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Set xlApp = Excel.Application
Set wks = xlApp.ActiveWorkbook.Worksheets(1)
Set rng = wks.Cells(2, 2) ' just a top left cell of a test range
Set rng = GetRowsSample(xlApp, wks, rng)
SaveRowsSample rng
End Sub
Public Function GetRowsSample( _
ByRef rxlApp As Excel.Application, _
ByRef rwks As Excel.Worksheet, _
ByRef rrng As Excel.Range)
Dim cnn As ADODB.Connection
Dim strCnn As String
Dim rstSrc As ADODB.Recordset
Dim avarSrcValues As Variant
Dim avarSrcValuesT As Variant
Dim rng As Excel.Range
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=Admin;" & _
"Data Source=" & mcstrMdbFullPath & ";" & _
"Mode=Share Deny None"
Set cnn = New ADODB.Connection
cnn.Open strCnn
Set rstSrc = New ADODB.Recordset
rstSrc.Open mcstrSql, cnn, adOpenStatic, adLockOptimistic, adCmdText
avarSrcValues = rstSrc.GetRows
rstSrc.Close
avarSrcValuesT = TransposeMatrixArray(avarSrcValues)
Set rng = rwks.Range(rrng, _
rwks.Cells(rrng.Row + UBound(avarSrcValuesT, 1), _
rrng.Column + UBound(avarSrcValuesT, 2)))
rng.Value = avarSrcValuesT
cnn.Close
Set GetRowsSample = rng
End Function
Private Function TransposeMatrixArray(ByRef ravar As Variant) As Variant
Dim avar As Variant
Dim lngRowIdx As Long
Dim lngColIdx As Long
ReDim avar(0 To UBound(ravar, 2), 0 To UBound(ravar, 1))
For lngColIdx = LBound(ravar, 1) To UBound(ravar, 1)
For lngRowIdx = LBound(ravar, 2) To UBound(ravar, 2)
avar(lngRowIdx, lngColIdx) = ravar(lngColIdx, lngRowIdx)
Next lngRowIdx
Next lngColIdx
TransposeMatrixArray = avar
End Function
Public Function SaveRowsSample(ByRef rrng As Excel.Range)
Dim cnn As ADODB.Connection
Dim strCnn As String
Dim rstDst As ADODB.Recordset
Dim lngIdx As Long
Dim lngRowIdx As Long
Dim lngColIdx As Long
Dim avarSrcValues As Variant
Dim avarNames As Variant
Dim avarValues As Variant
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=Admin;" & _
"Data Source=" & mcstrMdbFullPath & ";" & _
"Mode=Share Deny None"
Set cnn = New ADODB.Connection
cnn.Open strCnn
cnn.Execute mcstrDelSql1
Set rstDst = New ADODB.Recordset
With rstDst
.CursorLocation = adUseClient
.Open mcstrSql1, cnn, adOpenStatic, adLockBatchOptimistic,
adCmdText
Set .ActiveConnection = Nothing
End With
avarSrcValues = rrng.Value
ReDim avarNames(0 To rstDst.Fields.Count - 1)
ReDim avarValues(0 To rstDst.Fields.Count - 1)
For lngIdx = 0 To rstDst.Fields.Count - 1
avarNames(lngIdx) = rstDst.Fields(lngIdx).Name
Next lngIdx
For lngRowIdx = 1 To rrng.Rows.Count
For lngIdx = 0 To rstDst.Fields.Count - 1
avarValues(lngIdx) = avarSrcValues(lngRowIdx, lngIdx + 1)
Next lngIdx
rstDst.AddNew avarNames, avarValues
Next lngRowIdx
Set rstDst.ActiveConnection = cnn
rstDst.UpdateBatch
rstDst.Close
cnn.Close
End Function
BTW, if you'll find your customer wants a lot but doesn't have enough
resources then you may consider to use these 4TOPS Add-ns:
- 4TOPS Excel Link(http://www.4tops.com/excel_link.htm) - to export data
into MS Excel templates and to send these data;
- 4TOPS Excel Import Assistant (http://www.4tops.com/excelimport.htm) -
to import excel worksheets' data into MS Access databases
and even maybe 4TOPS Document Mangement
(http://www.4tops.com/downloaddocumentmanagement.htm).
These are stable software products and they were used in real life large
apps (one of them replaced big Oracle based app).
They are rather generic to fit your current customer tasks but of course
they may have limitations, which will not let you to use them.
And they export data from/import data to normalized databases. Useful
feature and implemented generic way.
They can be used as Add-ins by powerusers and even "weak"users.
They have simple APIs for developers and can be used as library
databases. (One test was to import 3000 MS Excel workbooks with several
worksheets with many source rows in one run - it worked well and it
would work well more - no memory leakage etc. problems - well tested -
and that's is so time consuming activity...)
BTW, when importing data from MS Excel worksheet forms, maybe having
additionally several "subforms", you may consider to create in your
templates special additional "worksheet(s) records reference
templates" - every cell of these templates rows' will have a reference
to the actual value of worksheet form/subform - then you will not need
to use named ranges to get data imported piece by piece (cell by cell -
it's slow) - you will be able to get whole source ranges' values at
once....
etc.
Shamil
----- Original Message -----
From: "John Colby" <jwcolby at ColbyConsulting.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Monday, January 23, 2006 11:40 PM
Subject: Re: [AccessD] Setting data into and getting data from Named
Range s
> Jim,
>
> I did, and thanks for the offer. I think that understanding that it
is the
> range object that actually does the work kind of removed my mental
> roadblocks. I was thinking that the name object should have abilities
that
> it doesn't have. I sent an email re "success" to the group.
>
> Again, thanks for the offer. If I run into any more roadblocks I will
keep
> you in mind. Kind of the last piece for right now is reading data
back out
> of a named range. I have code for doing that, though it is kind of
rough
> ATM.
>
> This is for a client who is documenting construction of a hospital.
> Apparently federal law requires that they collect documents for each
and
> every one of certain kinds of systems, paper and electronic, and store
them.
> My client did not understand the size of the job when he got involved,
nor
> when he got me involved.
>
> I am of course building a database to track all of the documents,
request
> them (electronically) from each contractor (via this Request For
Document
> spreadsheet, attached to emails) for each document, then as the
electronic
> documents are returned via attachments to the email I send, strip the
> attachments off and file them in an intricate directory structure out
on the
> hard disk. There will be literally thousands of documents, perhaps
tens of
> thousands. 6 contractors, 91 systems (though some are "copies", i.e.
7 "Air
> handling units"), and ~150 documents, although all docs are not
required for
> all systems. My db has to track what has been requested, store
"proof" that
> we did request them (each Excel RFD is saved in the same directory
> structure), track which docs are received back, report which docs are
"owed"
> by which contractors for which systems, and report which system's
documents
> have been completely received.
>
> We are trying to convince all the contractors to work with us in
naming the
> documents to a standard so that as the attachments come in I can parse
the
> doc name to see what system/document it is and "count it as received".
> There are just so many documents that to do this stuff manually would
invite
> chaos.
>
> On top of all that, the requirement is for both paper copies as well
as
> electronic copies, so the db has to track both. My client has to make
paper
> copies from Electronics where no paper exists, and make electronic
from
> paper where no electronic exist, and get it all entered in the db each
time.
>
> And finally, the main company (Pfizer) in charge of construction, had
the
> unfortunate experience of having their paper copies burned to the
ground one
> time. As a result I also have to ensure that the electronic copies
get
> automatically written to a local and offsite location, which I will be
doing
> via high speed internet. I have to track that each electronic
document gets
> written to the back up location as well - a usb hard disk on a
computer
> somewhere out on the internet. Since the internet can be unavailable,
I
> have to handle that as well, performing the write when the internet
becomes
> available.
>
> The paper in this case will completely fill a single wide trailer when
all
> is said and done, and in fact they have a single wide trailer that
they are
> using to collect the paper. When they are done they will hook up a
truck
> and haul the paper... Somewhere... Hopefully fireproof. ;-)
>
> This is where "time and expenses" really takes on meaning! I quoted
40
> hours to do what my client originally asked for - and DELIVERED that.
Of
> course my client did not understand the scope and so we soldier on.
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
> Sent: Monday, January 23, 2006 1:45 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Setting data into and getting data from Named
Range s
>
> John,
> I sent you an email, did you get it?
> Jim Hale
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com