Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Jan 2019
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access - Column headings - linked or imported excel table

    Hello,

    I've done several searches over the last few weeks hoping to find a solution to this. I suspect it doesn't exist but thought I would try here now that I've registered an account.

    When linking or importing an excel table, is there any way to define what row the column headings are in?

    I normally have a lot of notes or legend information in my first few excel rows. I don't need this info in the access database linking to the table, and of course access will interpret columns incorrectly if I don't get rid of it in the excel table.

    For the moment, I will create a new sheet that copies all the info from the originating sheet, and then link this to access. But I just don't like too many garbage rows or sheets since they tend to confuse me or others if we come back to the excel file at a later date.

    Thanks again. Excuse the flood of all my recent questions. I have a backlog of questions since I haven't been certain which help forum to join the past few years. So many out there, each with what seems their own strengths.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    10,155
    Thanks
    448
    Thanked 1,653 Times in 1,487 Posts
    Dave, it would seem prudent to me to have a second sheet cooked Into or Comments to contain this type of information, this getting it out of the way but still easy to find when needed.

    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

  3. #3
    New Lounger
    Join Date
    Jan 2019
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for the suggestion HTH,

    Comments I generally don't use for this info, both because they tend to get in the way, get turned off, or get moved away/hidden. For info like this I really want it front and centre at the top of the sheet. I also tend to keep it visible when I freeze panes as a constant reminder.

    Additional sheets, while certainly an excellent suggestion and one I have considered, tend to get ignored as well (by me mostly). I know this is silly, but they just tends to fall in my blind spot, whereas header rows are constant reminders to me. They also get edited more reliably if I make changes or remove a category.

    By the way, what do you mean by "Cooked Into" . I may be missing or misinterpreting one of your suggestions. If there was some functionality I am not aware of, I would be open to considering it.

    In the end however, the optimum would still be to have the flexibility in access to handle or disregards header rows if there was something in access that allowed it.

    Cheers

  4. #4
    WS Lounge VIP Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    1,493
    Thanks
    335
    Thanked 265 Times in 220 Posts
    I don't have time to test atm, but I wonder would temp hiding the extra rows work for importing?

    I assume it wouldn't work for linking, but don't know for sure.
    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

  5. #5
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    2,152
    Thanks
    177
    Thanked 214 Times in 202 Posts
    I tried this with a simple table with two header rows by using a named range, which was set up to ignore the first row and it worked (though I had to exit from the spreadsheet for it to be found). I'm not familiar with named ranges, or indeed, your data so you may have to experiment.
    Cartophilia rocks

  6. #6
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    2,152
    Thanks
    177
    Thanked 214 Times in 202 Posts
    Quote Originally Posted by Lugh View Post
    I don't have time to test atm, but I wonder would temp hiding the extra rows work for importing?

    I assume it wouldn't work for linking, but don't know for sure.
    I've just tried but it didn't work for me.
    Cartophilia rocks

  7. #7
    5 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    731
    Thanks
    73
    Thanked 100 Times in 93 Posts
    is there any way to define what row the column headings are in?
    Briefly, no. That's part of what makes a spreadsheet, a spreadsheet. Anything can go anywhere! Only databases put column headings in one place, and one place only, with 100% reliable consistency.

    There are a couple of minimal exceptions to this. When you create a "data sheet" in Excel, Excel forces headings to follow a simple-but-strict format (at the very top with no extra stuff above or below it). The other exception is when importing data, you can ask Excel to begin importing at a user-selectable row. Again, this is a simple service that merely ignores an arbitrary number of header rows.

    That's one reason why spreadsheet users tend to gravitate towards putting documentation/notes/prompts on their own sheet.

  8. #8
    New Lounger
    Join Date
    Jan 2019
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks access-mlb.

    I will look into named ranges. I'm a little too occupied at the moment, but will try and get back to this and test it.

    Cheers,
    Dave

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    10,155
    Thanks
    448
    Thanked 1,653 Times in 1,487 Posts
    Dave,

    If you are going to use a Named Range for your DB I'd suggest you look into using Dynamic Named Ranges so they will always adjust automatically to additions/deletions from the database. Post back if you need help with this.

    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

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

    access-mdb (2019-01-09)

  11. #10
    New Lounger
    Join Date
    Jan 2019
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello HTH,

    I'll keep that in mind while experimenting. Thanks.

    Dave

  12. #11
    New Lounger
    Join Date
    Jan 2019
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello RG and Malcolm,

    Thanks for the excellent advice. I was forced to get back to this task. Named ranges, and then editing to dynamic, seem to work wonderfully and is a perfect solution. I can now eliminate a confusing worksheet from my file.

    A couple things that have me scratching my head. One related to dynamic named ranges, and the other more general question to do with how access sees the worksheets when importing data.

    Dynamic range formula
    I used the following formula for my dynamic named range:
    =OFFSET('From Facies.xls'!$A$4:$AF$4,0,0,COUNTA('From Facies.xls'!$A:$A),32)

    The header row I have is in row 4. Thus the start at $A$4.

    The dynamic range seems to choose all the correct rows and columns, except at the end where it selects 2 empty rows. As a verification that I did not have some invisible data in these last two rows, I deleted the entire row and then checked the dynamic range again. It was still selecting the two empty rows after the end of the table/data

    How access sees worksheets
    This one seems to have changed since the last time I used access. It has been quite a while (almost 20 years) so my memory may be faulty.
    Access, when importing external excel tables, sees all the worksheets past the first worksheet as a named range.

    Capture1.JPG

    Capture2.JPG

    In the first image, the only named range is the first item which I just created with your help.
    The rest of the items on that list are actually worksheets. You can see in the second image that they do not show up when the "Show Worksheets" radio button is selected.

    Any ideas?

    Thanks again,
    Dave

  13. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    10,155
    Thanks
    448
    Thanked 1,653 Times in 1,487 Posts
    Dave,

    You don't want to count everything in Column A (A:A) only your database.
    Try this formula:
    =OFFSET('From Facies.xls'!$A$4,0,0,COUNTA('From Facies.xls'!$A4:$A1048576),32)

    Note: I removed the AF4 reference as the 32 covers the number of columns. Also if you're using 64bit Excel you'll need to adjust the 1048576 (max row for 32bit Excel).

    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

  14. #13
    New Lounger
    Join Date
    Jan 2019
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the follow up RG.

    As it is, my original formula seems to be doing what I require it to do barring the curiosity of the 2 additional rows showing up in the named range below the entered data. Everything in the access table seems proper, and the two additional rows are not been seen by access as any particular value. I will play a little more with your recommendations when I'm out of the crunch time. I'm curious though, why is the limit of the table required ($A1048576)? My named range seems to advance as I enter data without limit. Will something potentially fail without the limit set?

    I was wondering about that AF4 value since as you say it appears redundant. I checked with and without and the behaviour appears to be the same. It took me a few moments to wrap my head around the 32 column indicator at the end since I asked myself why microsoft chooses a number instead of a range to identify the extent of the table in this particular formula. Must be a good reason I'm not cluing into.

    Again, thanks for pushing me past this barrier. It is truly appreciated since I almost started working in the additional "access import sheet" I created when coming back to this file after several days away from it. Would have been wasted time, and created confusion if I hadn't caught myself. This spurred me to really work through the problem with the advice you two gave.

    Also, excuse my lack of brevity. When I'm short on time and rushed, my texts tends to get wordy

    Dave

  15. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    10,155
    Thanks
    448
    Thanked 1,653 Times in 1,487 Posts
    Dave,

    CountA(A:A) counts all filled in cells in column A. You don't want to do that due to your comments in Ross 1-3. You just want it to count from row 4 to the last row to allow for the max number of data rows for your version of Excel.

    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

  16. #15
    New Lounger
    Join Date
    Jan 2019
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello RG,

    I tried your edit and it corrects the issues with the two extra rows.

    All:

    I may have been in error in my above post. The static named range link to access works, but the dynamic named range link seems to confuse access. Here is what access is seeing when trying to choose the dynamic link instead of the static (highlited black):

    Capture4.JPG

    Unfortunate. It seemed like an elegant solution.

    Dave

Page 1 of 2 12 LastLast

Posting Permissions

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