Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2005
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Read contents wihout opening (Excel 2003 / VBA)

    Hi,
    I need to send excel files to my team members in which they are supposed to send their weekly reports. I save all the files that I send them in a particular directory (sent). Once the team member send me back the updated excel files, I save them in a separate directory (received). I would like to know of all the team members that were sent the format, who have not filled and sent it back.The name of team members are present in cell C4. I am planning to write a VBA code, which first opens all the files from the sent directory, one by one copies the name of team members (cell C4) in an excel column (each in a row )and then does the same steps for the received foder and copies the names of team members in another column (received) and then using vlookup find the difference (Defaulters) between two columns. My query - Can I accomplish this without having to open all the files as the file format is fixed and also protected to prevent format change.

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Read contents wihout opening (Excel 2003 / VBA)

    Hans
    I believe you missed an equal sign.
    <hr>Range("A" & lngRow).Formula ="'H:Sent[" & strFilename & "]Sent'!C4"<hr>
    Range("A" & lngRow).Formula =" = 'H:Sent[" & strFilename & "]Sent'!C4"
    Regards
    Don

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read contents wihout opening (Excel 2003 / VBA)

    Edited by HansV to correct error - thanks to Don Wells for pointing it out.

    You could loop through the files in the folder and create formulas referring to a cell in them without actually opening them. The instruction within the loop would look like this
    <code>
    Range("A" & lngRow).Formula ="='H:Sent[" & strFilename & "]Sent'!C4"
    </code>
    lngRow is a variable indicating the row of the target cell.
    strFilename is a variable containing the name of the file.
    Adjust the path as needed. Use Received instead of Sent for the other folder.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read contents wihout opening (Excel 2003 / VBA)

    You're correct; I have edited my previous reply.

    Thanks! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  5. #5
    Lounger
    Join Date
    Apr 2005
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Read contents wihout opening (Excel 2003 / VBA)

    Thanks. It works.

Posting Permissions

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