Results 1 to 8 of 8
  1. #1
    SLO is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2021
    Posts
    4

    Using a variable with xlsheet.range

    Hello I'm new at Access VBA, but making progress... Issue: I have a function that manipulates some table data. It merges a couple of tables. Once the work is done, I have it exporting to an Excel spreadsheet at a known location using xlSheet.Range (e.g. xlSheet.Range ("B4")). This all works just fine; pretty straight forward. I'm getting hung up in the following scenario. I now want to create a loop that will cycle through an unknown number of tables and export the result to a new spot in the Excel spreadsheet. I know each successive export will begin at a column three greater than the last. (B4, E4, H4 etc). I was hoping to somehow use a variable for the range value and increment it on every pass through the tables. The exports have to be side by side on the spreadsheet. Any ideas on how I might accomplish this? Thank you for your consideration.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I would look for the last used column and calculate the starting column from that?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    SLO is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2021
    Posts
    4
    Quote Originally Posted by Welshgasman View Post
    I would look for the last used column and calculate the starting column from that?
    Thanks for your reply Welshgasman. I hadn't considered anything along these lines. Are you suggesting I can write some VBA code that can reach out to the Excel spreadsheet and find an open cell? Can you give me an example of what that might look like?

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Just google for 'Excel find last column vba' and adapt that.
    I have done it many times for last row, but the logic is the same.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    SLO is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2021
    Posts
    4
    I may have to change the look of my sheet a bit, but this might do the trick. Thanks!

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Alternatively you can calculate the next each location by using a count of number of fields of each table to be exported. See this for a handy function to get you that:

    https://www.devhut.net/2015/10/09/ms...ds-in-a-table/

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    SLO is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2021
    Posts
    4
    Quote Originally Posted by Gicu View Post
    Alternatively you can calculate the next each location by using a count of number of fields of each table to be exported. See this for a handy function to get you that: https://www.devhut.net/2015/10/09/ms...ds-in-a-table/ Cheers, Vlad
    Thank you for the response Vlad. I'll take a look at your suggestion.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 7 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-02-2021, 09:15 AM
  2. Replies: 2
    Last Post: 05-14-2021, 08:39 AM
  3. Adding a Cell Range to a String Variable
    By RunTime91 in forum Access
    Replies: 3
    Last Post: 01-27-2018, 02:38 PM
  4. Replies: 12
    Last Post: 04-03-2012, 06:31 AM
  5. Replies: 0
    Last Post: 08-10-2011, 11:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums