1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Exporting Excel Data

Discussion in 'Business Applications' started by rowanburgess, Sep 26, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. rowanburgess

    rowanburgess Thread Starter

    Joined:
    Sep 26, 2007
    Messages:
    8
    Hi guys

    I am using Office 2007 and am unable to find this information else where on the form.

    Heres the scenario:

    I have 3000 different excel files that I wish to extract data from, possibly using VB. This data will be used to put into a table in Access (however, it may be imported into an excel sheet and copied accross if easier.)

    Each Excel file consists of exactly the same template, with different information enclosed in this template. I need a script that will extract the values of certain cells (the same cells everytime) in the sheet and input into the table (or final Excel sheet). This script needs to be able to run on every Excel file within a certain folder.

    In addition to this, it needs to grab the last date in a date column and copy this into a single date field at the top of the sheet.

    I know this a small request :eek: , but in theory if this is possible I would really appreciate someone to help me a little.

    Please note, I have no previous experience with VB, only amateur excel formulas.

    If there is anything else that would help people understand the issue I will be happy to forward any information.

    I dont expect to be spoon fed but I am on a really tight schedule, I hope that the answer can be beneficial to other users.

    Rowan
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    How about posting a sample Excel file? Use the "manage attachments" button at the bottom of the posting window to attach the file to your next post. If you don't have this option as a new forum user please email to Rollin_Again at hotmail dot com and I'll post for you.

    Regards,
    Rollin
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    To the board please.
     
  4. rowanburgess

    rowanburgess Thread Starter

    Joined:
    Sep 26, 2007
    Messages:
    8
    Ok, thanks for your reply Rollin.

    I have attached a sample Excel file and have put comments in the relevant field values that need to be exported. The important bit is it must be able to loop through multiple Excel files in a folder and complete the same task.

    Anything else let me know. Cheers

    Rowan
     

    Attached Files:

  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Please reply to each of the following questions:

    Will all the Excel files be contained in the same folder/directory? If so, will this directory contain any other files that the macro will need to ignore?

    Will the folder path containing the Excel files need to be "hard-coded" into the macro or would you like the user to be presented dialogue box that will allow navigation to the directory via an Explorer type window?

    Will this be a one time event or will you be running the macro and adding new records to the table on a regular basis? If this will be an ongoing event how do you want to ensure that the files in your directory don't get processed again? Should they be deleted or moved to another directory after being processed?

    Will the database table already exist or does the macro need to create a new table each time it is run? If the table will already exist will the file path and table name ever change?

    Will the macro only be adding new records to your table or will you have a need to modify existing records already contained in the table?


    Regards,
    Rollin
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    You thinking of going straight into an Access database Rollin?
     
  7. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Yes, I was planning on dumping the data into Access using VBA unless Rowan tells us otherwise.

    Rowan, which do you prefer, Access or Excel? It should be easy either way, the choice is yours!

    Don't forget to answer my questions in my previous post.


    Regards,
    Rollin
     
  8. rowanburgess

    rowanburgess Thread Starter

    Joined:
    Sep 26, 2007
    Messages:
    8
    They will all be contained in the same folder with no other files

    An explorer type window would be nice, but only if it is easy enough for you to build. If not, hard wired to 'C:\Users\rowan\Documents\state1' would be great

    I will be running the macro on a regular basis. The files that have been previously processed will be moved to a seperate folder - only new files ready to be processed will ever be in the folder. This can be done manually, maybe in the future I will look at getting it automated.

    I think the table will already exist - I want to be able to store the previously processed data in the same table. It will be used with a dot net application which will extract the new information from the table and mark that entry as complete

    If it is easier to create a new table every time please do so

    Existing records will stay as they are and will be marked as complete, each new record will be unique

    Can I ask, how exactly will this run? Is it like a small windows application that I run from clicking on an exe or will it be run within the excel files etc..

    Thank you very much for your time and effort Rollin, please let me know if there is any other information that would help. (y)
     
  9. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I have started working on the code and will try to post something for you by the end of the day.

    Regards,
    Rollin
     
  10. rowanburgess

    rowanburgess Thread Starter

    Joined:
    Sep 26, 2007
    Messages:
    8
    Ah great, thanks.

    Rowan
     
  11. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Here is some code to get you started. For now it will probably be easiest to add the code to a new Excel Workbook and run the macro straight from there. Just open a new Excel Workbook and click ALT + F11 to open the VBA editor. Click TOOLS --> REFERENCES and set reference to the latest version of "Microsoft DAO Object Library." After adding the reference click OK and then click INSERT --> MODULE and copy and paste the code into the blank module. Save the Workbook and then run the macro to be prompted for the location of the database and the directory containing your source files. The code assumes that the database already exists with a table containing 6 fields. Just change the line of the code that reads Set rs = db.OpenRecordset("YourTableName", dbOpenDynaset) to reflect the true table name. Hope this helps.

    Code:
    Public Sub AddRecords()
    
    Dim ShellApp As Object
    Dim objAccess As Object
    Dim rs As DAO.Recordset
    Dim db As Database
      
    vFilename = Application.GetOpenFilename(, , Title:="Select Database")
        
    Set db = Workspaces(0).OpenDatabase(vFilename)
    Set rs = db.OpenRecordset("YourTableName", dbOpenDynaset)
    
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Select Source Directory", 0, OpenAt)
        
    vFile = Dir(ShellApp.self.Path & "\*.xls")
    
    Do While vFile <> ""
    Workbooks.Open Filename:=ShellApp.self.Path & "\" & vFile
    
    rs.AddNew
    rs(0) = Range("D8").Value
    rs(1) = Range("D6").Value
    rs(2) = Range("D10").Value
    rs(3) = Range("D11").Value
    rs(4) = Range("H13").Value
    rs(5) = Range("I13").Value
    rs.Update
    Workbooks(vFile).Close
    vFile = Dir
    Loop
        
    rs.Close
    db.Close
    
    Set rs = Nothing
    Set db = Nothing
    Set ShellApp = Nothing
        
    End Sub
    Regards,
    Rollin
     
  12. rowanburgess

    rowanburgess Thread Starter

    Joined:
    Sep 26, 2007
    Messages:
    8
    Ok, it produced a couple of errors when I ran it.

    Firstly, I got to the point where I had to select the desired database, and it told me it was in the wrong format. So, I saved the database from Access 2007 back to Access 2003 - tried it again, no problems.

    The next step is selecting the source folder containing the files, here is the following error I received:

    Runtime error '3421'

    Data Type conversion error

    If this is due again to the excel files being saved in the wrong version I dont think there is too much I can do to change them :confused:

    Anyway, I will leave you to ponder over this one, I'm sure you already have the solution!

    Please let me know if there is anything else that would be useful to know.

    Thanks again,

    Rowan
     
  13. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Well I tested the code in Office 2003 and it works fine. My assumption is that the error is indeed due to the difference in the versions that are being used. I will run some tests and make necessary changes when I get to work since I have Office 2007 running on one of my machines there. Sorry for the delay.

    Regards,
    Rollin
     
  14. rowanburgess

    rowanburgess Thread Starter

    Joined:
    Sep 26, 2007
    Messages:
    8
    Ok, I have been playing around with it. I found that I had not set up the table correctly and it actually contained 6 fields (whether this should make a difference I'm not sure).

    So, I deleted one of the fields. I then ran the macro again - it again got to the stage of selecting the source folder, it opened the first excel file and produced the following error:

    'Runtime error 3265

    Item not found in this collection'

    Hopefully we are getting a little closer. I appreciate your time Rollin.

    Regards

    Rowan
     
  15. rowanburgess

    rowanburgess Thread Starter

    Joined:
    Sep 26, 2007
    Messages:
    8
    Please note - it produced the 'item not found in this collection' error whilst using then same workbook that I have passed to you for testing.

    Rowan
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/629471

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice