Extracting data from a Database...

[Anzulo]

Hi guys,
I hope you can help. This is the first time i post something although i see all the posts every day since 2 or 3 moths ago.
The situation is that every day i need to e-mail a report to my boss, this report reflects the status of the activities being developed by the team (around 40 guys). The data i analyse to reflect this status is stored in a database. This DB can be accesed two ways, one is using the designed user interface which is very useful because yo only enter your credentials and then go to the "Search Screen" where you enter all the criteria to look for the information you need, actually it allows you to create templates so every day you go and chose the same template and run the search, once you have the data the system allows you to save it as a CVS file.
The other way to acces the DB is via MS ACCES, in this case you also enter your credentials and go to "Queries", of course you can have a "Query" already customized so you can use it every day.
I have created a macro in Excel to process this data and send it to my boss automatically in an organized format so he can easily read it (that's the way my boss works!!).
This is the process i follow to send this daily report to him:
1.- Go to the DB(the one which has the user interface)and insert my LOGONID and PASSWORD.
2.- Go to the search menu and ENTER THE CRITERIA for the query.
3.- Got the data displayed in the screen.
4.- Go to the Commands menu and CHOSE "Save to file".
5.- I SPECIFY THE PATH where i want the data be saved AND ALSO THE FILE NAME which is the "today" date.
6.- I exit from the DB.
7.- Open the workbook which contains the macro.
8.- Open the CSV file and copy the data from here to the one which contains the macro.
9.- Run the macro which makes my boss be happy with this report in his inbox.

As you can see there are some steps before running the macro. I would like to remove those steps and only run a macro which could do all this process for me every day.
My problem is that my VBA knowledge does not allows me to write the code to go to the DB, enter my LOGONID and PASSWORD, specify the criteria for the query, tell the system to save the data and import it to the one which has the macro to send the report to my boss.
Do you have any idea how to tell the system go to the DB, enter the LOGONID and PASSWORD, search the data, save it and importe it to the book with the macro?

I hope i had expressed my self in a comprehensive way and also that you can help me.

Please if you need any other information let me know it.

Thanks in advance.
Respectfully.

[kkknie]

Anzulo,

I've done some work with ODBC calls to an access (.mdb) database, but something tells me this is not going to help you. If your database is a .mdb, let me know and I'll give it a go.

If this is not the case, you may want to look into using the sendkeys function to "automate" the login. Look at help files for this for more info (I have not done this for a while). If you don't have an ODBC driver for the database, you will probably resort to sendkeys in some way.

Maybe someone more familliar with this thing could help more...

K

[Anzulo]

KKKnie, Thanks for replying.
I have checked the database and this is what i found:
I opened the DB using MSACCES and in the "TABLES" menu i am able to see all the available tables to be used. I also see the one that i am interested in, i displyed all details for it and i saw the table name, the description, the modified date, the created date and the TPYE which says TABLE: LINKED ODBC.
In the other hand i remember when i "instaled" this DB in my PC i had to add or modify something in the ODBC Data Source Administrator. Now if go to this ODBC Data Source Administrator it says the DB name and the DRIVER which says Sybase System 11.

Now i hope this gives you more information about the DB i am using. Please reply.

PD> You are talking about "sendkey", is this an object or a method??

[Anzulo]

Still need help / suggestions about this.
Any help is welcome.

Thanks in advance

On 2002-05-14 11:47, kkknie wrote:
Anzulo,

I've done some work with ODBC calls to an access (.mdb) database, but something tells me this is not going to help you. If your database is a .mdb, let me know and I'll give it a go.

If this is not the case, you may want to look into using the sendkeys function to "automate" the login. Look at help files for this for more info (I have not done this for a while). If you don't have an ODBC driver for the database, you will probably resort to sendkeys in some way.

Maybe someone more familliar with this thing could help more...

K

[kkknie]

Sendkeys is a method that simply mimics keystrokes. I think
you might be able to automate the tasks required to open your
database, enter login id and password, enter your criteria,
save to a file and exit. The last three items on your list
should be able to be done using other methods.

This is what you must do. Go through all of the steps
you use to open, search, save and exit making sure to
use only keystrokes (no mouse input). Write these down
and then set up a macro in excel to mimic them.

I would recommend starting slowly (i.e. first just open
the database, then when that works, enter your id, etc.).
The excel help for sendkeys should assist in getting
all of the special keystrokes you need (like enter, alt,
arrow up, arrow down, etc.).

One thing about the sendkeys method is that it can get
ahead of itself. To keep this from happening, I always
put a bunch of waits between each keystroke. It takes
longer, but works better.

Here is an example of how I open a password protected file
in msaccess (note that this board converts
1 backslash into 2, so adjust accordingly:

Sub OpenDatabase1()

Shell ("D:Program FilesMicrosoft OfficeOfficeMSACCESS.EXE c:db2.mdb")
sleep (3)
SendKeys ("p")
sleep (1)
SendKeys ("a")
sleep (1)
SendKeys ("s")
sleep (1)
SendKeys ("s")
sleep (1)
SendKeys ("1")
sleep (1)
SendKeys ("2")
sleep (1)
SendKeys ("3")
sleep (1)
SendKeys ("~")

End Sub

Sub sleep(i)

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + i
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

End Sub

Try working on it for a while and let me know how it is going.

[ This Message was edited by: kkknie on 2002-05-15 07:10 ]

[klb]

Have you tried to use Data, Get External Data from within Excel?

You will need to have MSquery installed and the database set up as a Data source.

But you can do it all from within Excel.