Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25
  1. #16
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    2,175
    Thanks
    177
    Thanked 218 Times in 205 Posts
    Dave, had you closed the Spreadsheet before you did the import? When I tried with my simple experiment it couldn't find the named range until I had. Though I can't remember what error message it came up with. As I said before, I'm not familiar with named ranges.....

    Just looking again, mine wasn't dynamic, so my comment above may not be valid. However, on your last attachment, you seem to be linking rather than importing - is that significant?
    Last edited by access-mdb; 2019-01-14 at 03:57. Reason: added more info
    Cartophilia rocks

  2. #17
    New Lounger
    Join Date
    Jan 2019
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello Malcolm,

    True, I am linking. As you guessed, the dynamic one is the named range causing the issue. I've done the straight static named range you recommended, and it works fine regardless of link / import. That is what I am working with for now until I find time to figure out the dynamic method.

    Cheers,
    Dave

  3. #18
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    10,161
    Thanks
    450
    Thanked 1,655 Times in 1,488 Posts
    Dave,

    Ok, I genned up a test and here is what I found.

    Importing a Dynamic Range Name works. However, it did NOT respect the max number of columns. I had specified 3 columns but had data, no header, in column 4 and it imported it. Of course during the import process you can specify that it not be imported via a check box.

    UnWantedColumn.JPG

    Linking a Dynamic Range Name does not work for .xlsx files! However, it will work with .xls files, go figure! Of course it also did not respect the 3 column limit and the linking process does not give you the option of excluding columns! To remove unwanted columns you either have to alter the source Excel file or view the DB via a Query that only includes the desired columns.

    HTH
    May the Forces of good computing be with you!

    RG
    (still RetiredGeek on AskWoody.com)

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Lugh (2019-01-15)

  5. #19
    New Lounger
    Join Date
    Jan 2019
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the test RG.

    Unfortunately, I could not do a straight import of the dynamic name range from an xls either. Odd behaviour from both programs to not work a little more seamlessly.

    I'm also wondering if you folks see your additional sheets in the "show worksheet" or "show named ranges" section while importing. As I mentioned, Im seeing additional worksheets show up as named ranges.

  6. #20
    New Lounger
    Join Date
    Jan 2019
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi folks,

    Just don't know what to say. Had to go back to my access database today. I tend to rename excel files with revision numbers as I update or make major changes so I know I am working with the most recent data. This is important since I import this info into an external scientific software, and it is critical to be able to track back to the source data and ensure the link was updated with any new changes.

    Anyway, I went to update the link, to the excel file and the named range. Well that failed. Access just refused to find the file I just pointed it to. Though maybe this was due to the named range, so I tought I would just add a new linked table, go update my Queries with the new table name, and then delete the old table.

    Lo and behold, access refuses to see the static named range that it was seeing before. And oddly, the issue I had before with extra worksheets showing up as named ranges has corrected itself.

    But now I am left with trouble shooting the named range access can no longer see.

    Anyone have any thoughts / suggestions?

    Thx
    Dave

  7. #21
    WS Lounge VIP Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    1,550
    Thanks
    347
    Thanked 281 Times in 234 Posts
    Dave, it's probably time to clarify which versions of Access & Excel you're using—and which version of Windows or Mac you're on.

    There are minor differences, depending.
    Lugh.
    ~
    Dell Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
    i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 2 x 256G SSD, 4TB HD

  8. #22
    New Lounger
    Join Date
    Jan 2019
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Lugh,

    I am on the local install of Office 365 Business 32 bit. Windows 10 64 bit pro. All fully updated.

    I should mention, I recalled RGs comment about xls vs. xlsx. I did loose some conditional formatting functionality when I saved down to xls, but it has corrected my issues for now.

    Hard to understand why access does not play well with xlsx.

    Thanks,
    Dave

  9. #23
    WS Lounge VIP Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    1,550
    Thanks
    347
    Thanked 281 Times in 234 Posts
    Sorry, forgot to ask: is your database an ACCDB file, rather than MDB?
    Lugh.
    ~
    Dell Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
    i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 2 x 256G SSD, 4TB HD

  10. #24
    New Lounger
    Join Date
    Jan 2019
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry for the late reply Lugh. I've been deep into another project and now getting back to this one.

    the database is in laccdb format. True, I recall databases being mdb last time I worked with them which I assume is the analog of xls for excel?

  11. #25
    WS Lounge VIP Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    1,550
    Thanks
    347
    Thanked 281 Times in 234 Posts
    Quote Originally Posted by DaveYVR View Post
    mdb last time I worked with them which I assume is the analog of xls for excel?
    Yes. ACCDB was intro'd in 2007 along with the various ...X files for Excel, Word etc.

    Your Laccdb is a locked version of ACCDB, which Access opens when you open an ACCDB file—your 'real' file is ACCDB, Access auto-deletes the Laccdb when you [or the last remaining user] close the DB.

    Hopefully the experts will return and help you out with your latest problem in post #20, I don't have anything more to offer.
    Lugh.
    ~
    Dell Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
    i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 2 x 256G SSD, 4TB HD

Page 2 of 2 FirstFirst 12

Posting Permissions

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