Excel? Access?

[Ozgur]

Hi,

I can do everything in excel with modules. But I know nothing about
automation in access.
I have lots of big excel files and they are growing everyday. I need an
access file to gather all these excel files. Because report and form is a
great advantage of access.

I need only a simple sample that copies a variable (date, number etc.) from
excel or internet explorer and pastes it into a specific field in a specific
table.
Ex: I want to fill customer information in field2 of 5th record in field1
(in other words, I want to enter customer information of between 5th and
125th rows.)

Is it possible in access and is there any people to send me a sample for
this kind of data transfer?

Thanx

 

[ErezM via AccessMonster.com]

hi
here is an examle of excel automation from access (you need to add microsoft
excel to the references list in Tools->references)

Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim rs As New ADODB.Recordset

Set xlBook = xlApp.Workbooks.Open("YourExcelFileAndPathHere.xls")
rs.Open "Select * From Table1", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
With xlBook.Worksheets(Name Or Index (1 is first) of worksheet you want to
read from)

one way to access the data is:
rs!Field1 = .Cells(1, 4)

another way is:
rs!Field2 = .Range("A3")

then,
rs.Update
rs.Close
xlBook.Close
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
Set rs = Nothing

 

[Ozgur]

Hi ErezM,

Thanx for quick help and sample. I will work on it.

Is it possible to define a record (like a specific cell in excel)?
Ex: I want to fill only address information for 5th of 100 records.
In excel view;
1- macro will ask me for sirname to find. I will enter sirname.
2- macro will ask me for address to enter. I will enter address.
2- it will find the cell and row with that sirname. (ex: B25)
3- it will enter address into the cell right at sirname cell. (ex: C25)

 

[ErezM via AccessMonster.com]

hello again
if you're trying to update excel data from access then add this code inside
the With...End With block i worte
Dim msg as String
strName=InputBox("Enter SurName")
strAddress=InputBox("Enter Address")

.Range("A1").Select '(start from the first cell to be on the safe side)
.Cells.Find(What:=strName).Activate
Col = xlApp.ActiveWindow.ActiveCell.Column
Row= xlApp.ActiveWindow.ActiveCell.Row
.Cells(Row,Col+1)=strAddress

 

[Tony Toews [MVP]]

I have lots of big excel files and they are growing everyday. I need an
access file to gather all these excel files. Because report and form is a
great advantage of access.

Is this a one time transfer or an ongoing file transfer? If one time
you can do it manually. If ongoing then why not write the
application in Access and give the folks a front end to Access with
all the forms and reports in it they need.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Ozgur]

Hi ErezM,

Thanx. By your help, I started in access.

 

[Ozgur]

Hi Tony,

Thanx for your interest.
Unfortunately, not one time.

In fact, I wrote a module in excel.
It controls our special company software which is used by all our factories.
It transfers all data from screen (mouse can be addressed in coordinates)
that I select by checkboxes in my form in excel.
I am using this simple module for about 50 rows and 50 columns daily. (first
cell is primary key, cells at left (49 columns) are feeded by module.)

My needs in access programming are:
1- Record finding by loop (like "worksheet(1).Cells(RowX, 5).value". RowX is
my going up parameter.)
2- Choosing n'th cell at left (cell which I want to update.)

Özgür