Access and Excel

[andysgirl8800]

Is there a way to open an Excel file in the background, run an Excel macro to format the data as I need it, then import/append data into my Access table all with one button click?

 

[Cire]

Is there a way to open an Excel file in the background, run an Excel macro to format the data as I need it, then import/append data into my Access table all with one button click?

do a search..there are lots of posts on this. You will need to manipulate with recordsets and the the createobject functionality

 

[andysgirl8800]

thank you for replying! I've just spent some time looking through posts for transferspreadsheet, but still can't find anything that looks like it will help. Many of the posts that address my concern have no replies themselves. I've build the macro in my excel spreadsheet, and I know how to either create a hyperlink to the document, or VBA to open excel, but I am trying to eliminate user interface with excel. The only thing (I think) holding me up is trying to figure out how to run my excel macro on the excel document from access, and then import the results to my access table. Any suggestions would be greatly appreciated!

 

[SamDeMan]

you need to automate. i don't think that this can be taught in one posting, but the basic concept is that you would be working in excel through your VBE. its not rocket science, its just hard to explain it all. i have some postings which has code that use automation with word. do a search for my username, and you will find it. also, if you have an advanced access book lookup in the index or in the glossary "automation"

good luck,

sam

 

[andysgirl8800]

thanks...I'll see what I can find!

 

[andysgirl8800]

SamDeMan-
Thanx for some of the links. Started to work with some code that MAY help in the interim, until I can find something closer to what I want to accomplish. I do need some help translating/understanding some of the following code:
Dim startRow As Integer
Dim totalRows As Integer
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Dim i As Integer
Dim strTargetCC As String

startRow = Selection.Row
totalRows = Selection.Rows.Count

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\CAS\cost_accounting.mdb;"
' open a recordset
Set rs = New ADODB.Recordset

rs.Open "DIST_KEYS", cn, adOpenKeyset, adLockOptimistic, adCmdTable

r = startRow

i = 1
Do While i <= totalRows
If CcTargetCon = True Then
strTargetCC = Left(Range(CcTargetColumn & r).Value, 6)
Else
strTargetCC = Range(CcTargetColumn & r).Value
End If

With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("CC_ORIGIN") = CcOrigin
.Fields("REK_NR") = RekNr
.Fields("CC_TARGET") = strTargetCC
.Fields("PERCENT") = Range(PercentColumn & r).Value
.Fields("Not_Like") = NotLikeInd
.Update ' stores the new record
End With
i = i + 1
r = r + 1 ' next row
Loop
rs.Close
cn.Close

Specifically, I'm trying to interpret the area in blue. Does the .Fields("CC_ORIGIN")=CcOrigin refer to the field name in excel to be tranferred into the field name in access, or the other way around?

 

[SamDeMan]

what you are looking at in this code is adding records to a table. the way you do this in VBA is open a recordset and then you may want to test for some conditions and then you start a record, add values to the desired fields of that record and you .update which means it records that record.

a table in access can be an access table that is local on this database. you can link to an external table of another access database. you can further link to many other sources. one of these other sources is an excess spreadsheet. so technically you can be looking at a table which looks, feels and behaves like a table but its actually an excel spreadsheet. you can update records of the excel spreadsheet. However, this will only alllow you to add values or to take out values. there is another method that i thought you were referring to.

you can automate using VBA. this means that through your VBE window you can have access to all functionality of Microsoft Word, Excel or other programs. you may or may not see the window of word or excel, depending how you do this. for a sample of this please view this article:
http://support.microsoft.com/?kbid=285332

it is hard to read forums instead of textbooks. although we all try to that, but the fact is that not everything can be learnt on the forums.


on second thought, i would like to add that you are doing the right thing by trying to desipher the code that you pasted in. you must fully understand that code before you start with automation. as a word of percaution, you must carefull when you use automation, since you are running two programs at the same time even though one of them may not be visible.

good luck,

sam