Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Unanswered: get data from hundreds of .XLS

    a survey will return several hundred .XLS files.
    each file contains:
    ten text fields and
    50 groups of
    -importance 0..9
    -satisfaction 0..9
    tick-boxes.
    the file is protected so that the responder cannot shift boxes around.

    i would like access to trawl through any .xls that it finds in a specified directory and suck up the data. anyone got a strategy for this?

    even better (but impossible???) would be for access to trawl through a specified outlook .pst and suck the data directly from the .xls mail attachment. can this be done?

    izy

  2. #2
    Join Date
    Jan 2003
    Location
    Aberdeen, Scotland, UK
    Posts
    168

    Re: get data from hundreds of .XLS

    I can't actually do it but I have a plan. If you create linked tables to one of the Xls files run an append query and then change the linked file to be the next xls file.

    Since the Excel sheets are all the same it should work. If you create some code to do this in a loop (the hard bit) it may save you some time from all the copying and pasting.
    J.

  3. #3
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey izyrider,
    Here is a simple explanation for the Excel files, I don't think I can help you with the pst part. Create a form with a button and copy this code:

    Private Sub Command0_Click()
    Dim myDir As String
    Dim noadd As Boolean
    myDir = Dir("c:\mytest\*.xls", vbNormal)
    Do While myDir <> ""
    Select Case getMyFile("c:\mytest\" & myDir, noadd)
    Case Is = "added"
    End Select
    myDir = Dir
    Loop
    End Sub

    Then create a module and copy this code:

    Function getMyFile(myFile As String, noadd As Boolean) As String
    DoCmd.TransferSpreadsheet acLink, , "tmp", myFile
    DoCmd.RunSQL "INSERT INTO myTable SELECT * FROM tmp"
    DoCmd.DeleteObject acTable, "tmp"
    Kill myFile
    End Function

    You will have to modify to meet your needs but this basically will check the folder for any .xls files and then perform some task with that file and then delete it. Like I stated this is a very simple procedure and you can pretty much make it as advanced as you want. Let me know if you have any questions or if you would like any ideas on making it automated.

    Later, Kal

  4. #4
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey izyrider,
    Here is a simple explanation for the Excel files, I don't think I can help you with the pst part. Create a form with a button and copy this code:

    Private Sub Command0_Click()
    Dim myDir As String
    Dim noadd As Boolean
    myDir = Dir("c:\mytest\*.xls", vbNormal)
    Do While myDir <> ""
    Select Case getMyFile("c:\mytest\" & myDir, noadd)
    Case Is = "added"
    End Select
    myDir = Dir
    Loop
    End Sub

    Then create a module and copy this code:

    Function getMyFile(myFile As String, noadd As Boolean) As String
    DoCmd.TransferSpreadsheet acLink, , "tmp", myFile
    DoCmd.RunSQL "INSERT INTO myTable SELECT * FROM tmp"
    DoCmd.DeleteObject acTable, "tmp"
    Kill myFile
    End Function

    You will have to modify to meet your needs but this basically will check the folder for any .xls files and then perform some task with that file and then delete it. Like I stated this is a very simple procedure and you can pretty much make it as advanced as you want. Let me know if you have any questions or if you would like any ideas on making it automated.

    Later, Kal

  5. #5
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey izyrider,
    Here is a simple explanation for the Excel files, I don't think I can help you with the pst part. Create a form with a button and copy this code:

    Private Sub Command0_Click()
    Dim myDir As String
    Dim noadd As Boolean
    myDir = Dir("c:\mytest\*.xls", vbNormal)
    Do While myDir <> ""
    Select Case getMyFile("c:\mytest\" & myDir, noadd)
    Case Is = "added"
    End Select
    myDir = Dir
    Loop
    End Sub

    Then create a module and copy this code:

    Function getMyFile(myFile As String, noadd As Boolean) As String
    DoCmd.TransferSpreadsheet acLink, , "tmp", myFile
    DoCmd.RunSQL "INSERT INTO myTable SELECT * FROM tmp"
    DoCmd.DeleteObject acTable, "tmp"
    Kill myFile
    End Function

    You will have to modify to meet your needs but this basically will check the folder for any .xls files and then perform some task with that file and then delete it. Like I stated this is a very simple procedure and you can pretty much make it as advanced as you want. Let me know if you have any questions or if you would like any ideas on making it automated.

    Later, Kal

  6. #6
    Join Date
    Jan 2004
    Posts
    9

    Use SQL*XL and VBA

    Hi,

    You may be able to do this reasonably easy from within Excel and using SQL*XL.

    Strategy:
    Make an Excel VBA macro to loop through your spreadsheets opening each one in turn.
    After opening a spreadsheet you could issue an insert to make a new row with the data from the spreadsheet.

    In code it might look something like this:

    connect_using_sqlxl
    For each file in get_xls_files
    Set wks = file.Worksheets(1)

    sql = "insert into mytable (col1, col2, col3) values "
    sql = sql & "( " & wks.Range("A10").Value
    sql = sql & ", " & wks.Range("D40").Value
    sql = sql & ", " & wks.Range("F55").Value
    sql = sql & ") "

    SQLXL.Database.ExecuteSQL sql
    SQLXL.Database.Commit
    Next file
    disconnect_using_sqlxl

    You can macro record the connect/disconnect steps.

    Let me know when you need further guidance. You can get in touch through our webforms on www.oraxcel.com

    Learn more about SQL*XL at www.oraxcel.com

    Best regards, Gerrit-Jan Linker
    Linker IT Consulting Limited
    www.oraxcel.com

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    thanks for the reply gjlinker.

    my problem was a whole year ago and it got itself fixed.

    my fix:
    put *.XLS file names from user-defined path into a table
    get user-defined A9 references for cells of interest, with a user-defined name for each pair of cells
    convert A9 references to RC references
    loop thru files till EOF
    sourcexl.workbooks.open filespec
    loop thru cells till EOF
    data = sourcexl.application.cells(thisrow, thatcolumn).value & " "

    i gave up worrying about attacking the .pst from code: this survey only occurs every 2 years and it was daft to spend possibly dozens of hours coding compared with 30 minutes of manual SaveAs.

    i had a quick look at your link: interesting stuff!
    i'll have a closer look later.


    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •