Results 1 to 5 of 5
  1. #1
    Benj is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    33

    letter head in excel


    Good day! am importing records from excel using DoCmd.TransferSpreadsheet. The problem is, the excel file has rows of letterhead and a footer which i have to delete first before I import them. Is there a way to import them even with the letterhead and footer? thanks in advance!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Not with TransferSpreadsheet. Could with Excel automation code. For ideas review http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

    Or import the rows then delete from table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You can only skip the first row, but I believe you can import from a named range into Access if the sheet has a named range. Don't believe I've ever tried the range thing though.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Ooops, yes, I forgot about specifying named range or range by cell reference.

    Do you have control over production of this spreadsheet?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you know the range you can also do this with sql - e.g.

    Code:
    SELECT *
    FROM (SELECT * FROM [sheet1$A3:D50] AS xlData IN 'C:\path\filename.xlsx'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes])  AS XL;
    and if you are not sure of the range, but can identify a row from the contents of a specific column then something like this where a column contains numeric data
    Code:
    SELECT *
    FROM (SELECT * FROM [sheet1$A:D] AS xlData IN 'C:\path\filename.xlsx'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes] WHERE isnumeric(somecolumn))  AS XL;

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

Similar Threads

  1. Importing Letter head
    By George in forum Reports
    Replies: 4
    Last Post: 06-25-2017, 03:03 PM
  2. soccer table head-to-head query
    By sgramesh75 in forum Queries
    Replies: 2
    Last Post: 03-29-2017, 11:09 PM
  3. in Way over my head HELP
    By 91hrdbdy in forum Programming
    Replies: 8
    Last Post: 11-26-2013, 01:06 PM
  4. Replies: 4
    Last Post: 09-18-2012, 05:07 AM
  5. In over my head
    By TylerB in forum Access
    Replies: 2
    Last Post: 05-04-2011, 04:07 PM

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