UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Links To 255 Fields In An Excel Table Even Though Only 16 Have Data In Them, Any Version    
 
   
alancossey
post Jul 22 2019, 01:33 PM
Post#1



Posts: 559
Joined: 26-September 05
From: Norfolk, UK


Hi everyone,
Does anyone know why on earth my Access database links to an Excel worksheet and setting up 255 fields when only the left hand 16 have got data in them, please? With other linked tables to it only links to ones with data in them.

It wouldn’t really matter to me if this happened, but it then leads to Access getting upset when I create a query from this linked table and some other ones as well as it then whinges about having too many fields defined.

I'm using Office 365 Access.

Many thanks,

Alan Cossey
Attached File(s)
Attached File  2019_07_22_19_21_23.jpg ( 173.5K )Number of downloads: 13
 

--------------------
Alan Cossey
Premier Data Technologies Limited
www.pdtl.co.UK
Go to the top of the page
 
theDBguy
post Jul 22 2019, 01:47 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,407
Joined: 19-June 07
From: SunnySandyEggo


Hi Alan. Are you using the Wizard to link to the Excel file? Are you linking to different Excel files regularly? Meaning, the file you linked to earlier is no longer used, so you have to link to a new one? Where is the Excel file coming from? How was it generated? The only explanation I can offer for something like this happening is computers can "see" things our eyes can't. So, my guess is there's something in those empty columns that they don't appear empty to the computer.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
haresfur
post Jul 24 2019, 12:09 AM
Post#3



Posts: 303
Joined: 4-April 12
From: Bendigo, Australia


You could try highlighting all the blank columns, then right-click and delete all of them. That way if there is anything hidden that makes them seem like they have data in them, it should be deleted.

--------------------
-- Evan
Go to the top of the page
 
dale.fye
post Jul 24 2019, 07:11 AM
Post#4



Posts: 161
Joined: 28-March 18
From: Virginia


I've had this happen in the past, results from data which was deleted but not cleared from within the worksheet.

I don't have the code handy, but what I ended up doing was opening the Excel file via automation, selecting all of the columns to the right of the last "valid" column, and use the ClearAll feature of Excel to clear everything out of those columns. Then you save and close the Excel file and then link it to Access.


Dale

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
WildBird
post Jul 24 2019, 08:08 PM
Post#5


UtterAccess VIP
Posts: 3,620
Joined: 19-August 03
From: Auckland, Little Australia


Agree with everything here. There seems to be some phantom data in the excel sheet. Depends how you are getting the file, but you could copy the data (16 columns) and paste into a new worksheet and try that.


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
ADezii
post Jul 25 2019, 07:55 AM
Post#6



Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. As suggested earlier, you can Open the Workbook via Automation Code and DELETE all Columns to the right of 16 ('P'). Naturally, this assumes that there is absolutely, positively NO Data in those Columns you wish to DELETE. I create a Demo for you that:
    • Opens the Workbook Demo.xlsx, via Automation Code, in the C:\Test Folder, namely: 'C:\Test\Demo.xlsx'.
    • DELETES Columns 17 ('Q') thru 255 ('IU'). The Columns to DELETE can easily be modified by changing the Value of one or both of the Constants.
    • Saves the Workbook.
    • Close the Instance of Excel and performs some Clean Up chores.
  2. Code Definition using Early Binding:
    CODE
    Dim appExcel As Excel.Application
    Dim wkb As Excel.Workbook
    Dim sht As Excel.Worksheet
    Dim strFrom As String
    Dim strTo As String
    Const conCOL_FROM = 17
    Const conCOL_To = 255

    Set appExcel = New Excel.Application
    Set wkb = appExcel.Workbooks.Open("C:\Test\Demo.xlsx")
    Set sht = wkb.Worksheets("Sheet1")

    With sht
      'Convert Number Columns to Letters
      strFrom = Split(.Cells(1, conCOL_FROM).Address, "$")(1)
      strTo = Split(.Cells(1, conCOL_To).Address, "$")(1)
      .Columns(strFrom & ":" & strTo).Select
      appExcel.Selection.Delete Shift:=xlToLeft
    End With

    wkb.Save
    wkb.Close
    appExcel.Quit
    Set sht = Nothing
    Set wkb = Nothing
    Set appExcel = Nothing
  3. Once this Code is executed, you can even dynamically Link to the External Workbook.
  4. Hope this helps.

Go to the top of the page
 
alancossey
post Jul 25 2019, 01:54 PM
Post#7



Posts: 559
Joined: 26-September 05
From: Norfolk, UK


Thanks everyone who replied. Yes, it was phantom data in the sense of the top row having been formatted or something like that. Anyway, I just deleted the columns to the right of the ones I wanted and then it linked OK.

Much appreciated.

--------------------
Alan Cossey
Premier Data Technologies Limited
www.pdtl.co.UK
Go to the top of the page
 
theDBguy
post Jul 25 2019, 01:57 PM
Post#8


Access Wiki and Forums Moderator
Posts: 76,407
Joined: 19-June 07
From: SunnySandyEggo


Hi Alan. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th October 2019 - 06:49 PM