Results 1 to 7 of 7

Thread: Transfer HELP!

  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Unanswered: Transfer HELP!

    I really need some help or guidance. I need to import 42 spreadsheets into my database everyday. i use a transferspreadsheet DoCmd in VBA but i was thinking that if i used a 'looping' procedure of somekind this would be easier. All of my spreadsheest are labeled ####Aging.xls. i would need something that could rotate the first four numbers (like an array?) i have never used an array can someone help?

  2. #2
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    I'm not familiar with the do command but this will loop nicely anyway.


    dim i = integer
    for i = 1 to 40

    do cmd import(i & "Aging.xls") *
    next i

    *This line of code work won't but just to give you an idea, as you seem to already have the write code for this line. Just replace the #### with i.

  3. #3
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    Thanks for the response. I think its a little more involved... The #### represent specific numbers not just 1 ,2 ,3, 4... I think an array would work because it would let me store these location numbers in one group. Does any one agree with my array theory? I am new to using arrays...

  4. #4
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Sure I could help you with an array too if you were to provide more details.

    dim filenumber(42) as integer //Define an integer with 42 integer elements
    dim i as integer

    //Initalize each value in your array
    filenumber(1) = 15
    filenumber(2) = 85
    ...
    filenumber(42) = 293

    //Loop through all the array records
    for i = 1 to 42

    do cmd import(filenumber(i) & "Aging.xls")

    next i

  5. #5
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    WOW! Thanks! I will try this tonight and get back to you tomorrow. THANKS!!!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Where are you setting / getting your numbers for the spreadsheets
    Depending on what you are doing with your spreadsheets it my make sense to read the numbers from a table, ok I know it sounds over the top to use a relational database for this but consoder the situation that some bright spark decides to change the number of spreadsheets to process.

    If they are set via a form then adding or removing spreadhseets from the list becomes a trivial non programming matter - it could be deployed using a MDE database rather than a MDB. It also shifts the responsibilty for making the changes to the processing the users responsibility rahter than yours. Sods law always suggests that the bright spark forgets to mention to you the changes untill the project is way past critical delivery time and you are about to go on holiday

    just a thought

  7. #7
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    Thanks Lith- The 'array' help worked perfectly!!!

    Healdem- I may change to a table and create the relationship like you suggested. Right now i just wanted this to work. We plan on adding alot of locations in the coming year so you suggestion may prove to be valuable. THANKS!

Posting Permissions

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