Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Oct 2003
    Posts
    18

    Unanswered: Getting file names

    Hi All,

    I tried to find this topic so forgive me if it has been covered elsewhere.

    I receive data files securely through an application which I've been able to call through Access. The files arrive in a designated folder. What I would like to do is have Access get a list of these files and place them perhaps in a table so I can then sequentially go through and process them (they're csv flat files) using a mix of VBA and queries.

    The part I can't figure out is how to get Access to collect the file names and put them somewhere where I can manipulate them. My current method is using an 'open file' dialogue box where I manually select one file name at a time. I would like to be able to automate the process both because the files are always in the same place and because as it only takes one name at a time, this can be tedious. I should note that I cannot predict the file names (or else there wouldn't be a problem).

    Any ideas?

    Thanks in advance.
    Flipper
    "Don't you hate Perry's wife?"

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider using the file system object to iterate through thte files inthat directory
    you do need tomake sure you have the right references in the VBA code.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2003
    Posts
    18
    Thanks!

    I'm self taught so could you be a little more specific. Sorry, I'm a bit of a hack.

    Appreciate your patience.

    Flipper
    "Don't you hate Perry's wife?"

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by healdem
    you do need tomake sure you have the right references in the VBA code.
    Is there anyway to stick a reference on in VB code?
    I have a small database that needs the Microsoft Office 10.0 Object Library reference to work.
    When it comes to giving this to a customer I don't want to have to go and turn the reference on for them - I want to automate it!

    Any ideas?

    - GeorgeV
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by georgev
    Is there anyway to stick a reference on in VB code?
    I have a small database that needs the Microsoft Office 10.0 Object Library reference to work.
    When it comes to giving this to a customer I don't want to have to go and turn the reference on for them - I want to automate it!

    Any ideas?

    - GeorgeV

    i m not sure you can attach references dynamically (MSDN doesn't seem to think so)
    however the good news is that the Office Object library is a core library that is part of the office installation. So your customers / users will have access to that library by default.. you shouldn't need to select it. the problem is versioning.. if they are not using 2000 or Office XP or a later version then if you write soemthing that is specific of Version 10 you may have a problem. If they do then its a manual procedure... you could trap for an office 10 error (it must be possible get the library version from somewhere and display a warning box syaing in effect you user must attach the library)
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by healdem
    however the good news is that the Office Object library is a core library that is part of the office installation. So your customers / users will have access to that library by default.. you shouldn't need to select it. the problem is versioning.. if they are not using 2000 or Office XP or a later version then if you write soemthing that is specific of Version 10 you may have a problem. If they do then its a manual procedure... you could trap for an office 10 error (it must be possible get the library version from somewhere and display a warning box syaing in effect you user must attach the library)
    I run the full version of MSOffice 2003 and I had to turn MSO 10.0 Office Library on > this leads me to presume it is not on by default.

    I had a go at trapping the error but the code just fails on this line
    Code:
    Dim fDialog As Office.FileDialog
    So I'm kinda stuck on this one too!
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you can add references from code to MDB (using .addfromfile or .addfromguid - the latter is illustrated here to add DAO ref (also illustrates the use of version 0,0 to reference the latest version existing on the client machine)).

    you cannot add references to MDE

    long-term you will be happier if you late-bind to office/excel/word etc (you wont need a reference at all). see some discussion here using Excel as an example.

    izy
    Last edited by izyrider; 02-01-07 at 07:17.
    currently using SS 2008R2

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Application.References.AddFromGuid "{00025E01-0000-0000-C000-000000000046}", 0, 0
    Please can you explain this bit to me please?
    "{00025E01-0000-0000-C000-000000000046}"
    This refers to?
    How would I obtain the code for MSO 10.0 Object library?
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by georgev
    Code:
    Application.References.AddFromGuid "{00025E01-0000-0000-C000-000000000046}", 0, 0
    Please can you explain this bit to me please?
    "{00025E01-0000-0000-C000-000000000046}"
    This refers to?
    How would I obtain the code for MSO 10.0 Object library?

    ..that bit is the GUID of what ever library or reference in the repository / registry
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Didn't I just post on FSO's a while back????
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by healdem
    ..that bit is the GUID of what ever library or reference in the repository / registry
    And how would I find that out?
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Tra la la:
    Code:
    ?Application.References(1).Guid
    ?Application.References("VBA").guid
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ran:
    Code:
    MsgBox Application.References("VBA").guid
    which bought me a lovely textbox with one of these "GUID"s

    then I sat back and went "hmm, how do I get the one I wanted? then how do I test that it's worked?"
    and after more humms and sighs I decided to reply here
    George
    Home | Blog

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Clue- you can also retrieve via the index #. And another property is the name.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Oct 2003
    Posts
    18
    SOO Anywhoo!

    Not finding my answer here, I found a UK users forum who pointed me toward the Dir function. I guess that's pretty basic stuff but I hadn't run across it before. Probably other ways to do what I'm doing but I couldn't find it here.

    Just a humble word of suggestion: it's frustrating to have replies on your post that don't actually address your problem. It seems like all of you knew the answer I was looking for but nobody mentioned it.

    Also the search engine for the site used to work better. The Google based search doesn't seem to allow limiting searches to a particular section of the forum (like Access), at least that was my experience when using 'search this forum'. Lot's of responses for other applications came up that weren't relevant.

    Appreciate the resource but it didn't help this time around.

    Flipper
    "Don't you hate Perry's wife?"

Posting Permissions

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