excel, access and VB

[bego]

Hi,
i am in trouble!! I am getting an excel file every month (in the same format) with some data to analyse and transfer to a database (possibly Access) so i can keep a history of the data and the analysis.

I need to do an application that will allow users to select the new file, perform the calculations on the data and transfer to the access database and produce a report in excel format.

I have done this in Access (i am quite ok with Access db, but have not got any idea of Excel) with simple SQL but i need to get this done automatic.

Please, anybody can help? Would really be gratefull.

Thanks very much

[Ecniv]

...I have done this in Access (i am...

The problem??



You need
Access db (which you say you have)
A folder (probably on a shared drive) for the excel files
A folder or option to save/export report

Choices:
Build all the calculations into the db, export either csv, excel or outputReport. I personally would do it all in the mdb and only use excel to display the results if neccessary at the end. Excel is handy in that once you get how to code in it, you can use the record macro to give you a base and connect from the mdb.

Enough waffle. What exactly is the problem if you have the db doing it already?

Vince

[bego]

thanks for your reply.
The problem is how to import the excel file at runtime. I am very new on VB and don't know how to import the new excel file to the access db (if this can be done) at runtime and then update the ado conection (or if i even need to do it!). Sorry, i didn't say in my first post that i have just started programming in VB so everything is very new.

What i have done in Access is just manually import the excel file and then work the calculations and dump it in a new table.

Thanks very much for your help.

[Ecniv]

Ahhh

Transfering spreadsheet
(docmd.transferspreadsheet in access VBA)
I think you can call this via ADO, once you've connected to the access db (see Able computing link below for connection info).
I know you can via DAO, but thats not in this question.


You could use ADO to open the spreadsheet then traverse through it adding records t othe Access db. You could learn the file format of excel and import it via code, again grabbing information and putting it into Access.

Various routes to take. Perhaps tutorials might be an answer, to see how they did it in them then expand to encompass your spreadsheet. Have a search online for MS Access and transferring spreadsheets via code.


Have an experiment and post up your code once you have it partially working. People can then assist you in learning what you need to complete the task at hand.


Vince

[Killazzz]

If the data always comes to you in the same way on a certain directory.
Just open the excel file through VB(A). Start reading the excel file row by row and insert the subsequent values into the table.
Then start your calculations.

This is what I found in the Access Help

' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName as String, _
                    ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd as Long,ByVal wMsg as Long, _
                    ByVal wParam as Long, _
                    ByVal lParam As Long) As Long

Sub GetExcel()
    Dim MyXL As Object    ' Variable to hold reference
                                ' to Microsoft Excel.
    Dim ExcelWasNotRunning As Boolean    ' Flag for final release.

' Test to see if there is a copy of Microsoft Excel already running.
    On Error Resume Next    ' Defer error trapping.
' Getobject function called without the first argument returns a 
' reference to an instance of the application. If the application isn't
' running, an error occurs.
    Set MyXL = Getobject(, "Excel.Application")
    If Err.Number <> 0 Then ExcelWasNotRunning = True
    Err.Clear    ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
    DetectExcel

' Set the object variable to reference the file you want to see.
    Set MyXL = Getobject("c:\vb4\MYTEST.XLS")

' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
    MyXL.Application.Visible = True
    MyXL.Parent.Windows(1).Visible = True


     'Do manipulations of your  file here.

'so you need a row and colum counter variable.
'you can also determine the last cell in the sheet 
'record a macro in excel and see what code it gives when you do a ctrl end or something
insert this code here and set the row and column to this value.
start looping the excel sheet

for i = 1 to row_count
sql="insert into table (field1,field2) values ("

 for y=1 to columncount
 sql=sql & myXL.activesheet.cells(i,y) & ", " ' or something like this
 
 next y
'before going to the next row insert the data into the db
sql=mid(sql,1,len(sql)-2) 'as the last two characters will be , space
sql=sql & ");"

currentdb.execute(sql)
sql=""

next i




If ExcelWasNotRunning = True Then 
        MyXL.Application.Quit
    End IF

    Set MyXL = Nothing    ' Release reference to the
                                ' application and spreadsheet.
End Sub

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
    Const WM_USER = 1024
    Dim hWnd As Long
' If Excel is running this API call returns its handle.
    hWnd = FindWindow("XLMAIN", 0)
    If hWnd = 0 Then    ' 0 means Excel not running.
        Exit Sub
    Else                
    ' Excel is running so use the SendMessage API 
    ' function to enter it in the Running Object Table.
        SendMessage hWnd, WM_USER + 18, 0, 0
    End If
End Sub