Is this possible? And If not, Which Language would do it?

[jonx]

Hello everyone, I am starting a project for a company, and
they would like for me to automate rate sheets for them. Basically what I would be doing is creating a program that will download all the rate sheets ( which are all excel worksheets) and combine them into one really nice looking rate sheet.

Is this possible?

The program would have to download them, so I am guessing I would have to use some sort of sockets to do that (The sight that i will be downloading from uses SSL (https) ), and then I would have to retrieve data and manipulate data within the excel worksheets, so I am guessing there is some sort of library for this as well?

Is this possible? Thanks

 

[chiph]

Yes, it's possible.
You'll need to write a fair amount of code. It won't be easy.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[BrainB]

Not difficult using Visual Basic for Applications (eg. an Excel macro). Not sure what you mean about the "download" bit though. Need to get the files into a folder on disk. I suggest you post your more specific queries in the "Visual Basic for Applications (Microsoft) forum".

Here is some sample code that opens up all workbooks in a folder and consolidates data into a single worksheet -

'===============================================
'- Generic code for processing all
'- workbooks contained in a folder.
'- workbooks must be the only ones in the folder.
'- Change "Sub Transfer_data()" etc. as required.
'------------------------------------------------
'- in this example to summarise tables in all
'- worksheets in all workbooks :-
'- worksheets must be contain tables which are
'- identical to the master, headings in row 1.
'- master sheet is remade each time.
'- run this code from the master book
'- by Brian Baulsom (BrianB) January 1st.2004
'----------------------------------------------
Dim ToBook As String
Dim ToSheet As Worksheet
Dim NumColumns As Integer
Dim ToRow As Long
Dim FromBook As String
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim LastRow As Long
'-
'--------------------------
Sub FILES_FROM_FOLDER()
'--------------------------
    Application.Calculation = xlCalculationManual
    ChDrive ActiveWorkbook.Path
    ChDir ActiveWorkbook.Path
    ToBook = ActiveWorkbook.Name
    '---------------------------
    '- MASTER SHEET
    '---------------------------
    Set ToSheet = ActiveWorkbook.Worksheets(1)
    NumColumns = ToSheet.Range("A1").End(xlToRight).Column
    ToRow = ToSheet.Range("A65536").End(xlUp).Row
    '- clear master
    If ToRow <> 1 Then
        ToSheet.Range(Cells(2, 1), Cells(ToRow, NumColumns)).ClearContents
    End If
    ToRow = 2
    '------------------------------
    '- main loop to open each file
    '------------------------------
    FromBook = Dir("*.xls")
    While FromBook <> ""
        If FromBook <> ToBook Then
            Application.StatusBar = FromBook
            Transfer_data
        End If
        FromBook = Dir
    Wend
    '-- close
    MsgBox ("Done.")
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
End Sub
'---------------------------------------------------------------
'- CHANGE THIS CODE TO DO WHAT YOU WANT TO THE OPENED WORKBOOK
'----------------------------------------------------------------
Sub Transfer_data()
    Workbooks.Open FileName:=FromBook
    For Each FromSheet In Workbooks(FromBook).Worksheets
        LastRow = FromSheet.Range("A65536").End(xlUp).Row
        '------------------------------
        '- copy paste to master sheet
        FromSheet.Range(Cells(2, 1), Cells(LastRow, NumColumns)).Copy _
            Destination:=ToSheet.Range("A" & ToRow)
        '------------------------------
        '- set next ToRow
        ToRow = ToSheet.Range("A65536").End(xlUp).Row + 1
    Next
    Workbooks(FromBook).Close savechanges:=False
End Sub
'==== EOP ======================================

Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================