Share via

need to edit data before importing everytime

Anonymous
2012-12-05T10:35:18+00:00

I have a database which I need to import an excel spreadsheet of data into which is not in the correct format.

It has a row above the column headings and it has a row below the data which need deleting before it will import correctly.

I can't link to the data source that the data is generated from so I have to use the excel spreadsheet.

The spreadsheet is updated daily, so I need to edit the spreadsheet daily so that it will import correctly.

Is there anyway I can say to Access please import this spreadsheet automatically, but before you do - delete the row above the column heading and below the data?

Any suggestions explained in simple terms (as I am a novice), would be very welcome.

Many thanks.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

11 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,830 Reputation points Volunteer Moderator
    2012-12-05T16:39:54+00:00

    A new spreadsheet is produced for a day or a date range which has to come into Access.  You would have to name the range on every new spreadsheet surely?

    It depends on how the spreadsheet is created. If its automatically generated from some other program, than yes, it may be necessary to name the range all the time. If data is copy and pasted into the same sheet, then the named range should continue.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-12-05T14:31:26+00:00

    A new spreadsheet is produced for a day or a date range which has to come into Access.  You would have to name the range on every new spreadsheet surely?

    Was this answer helpful?

    0 comments
  3. ScottGem 68,830 Reputation points Volunteer Moderator
    2012-12-05T14:12:08+00:00

    Sure the range can be named. A named range is not static. If you insert rows, the named range expands. If you add rows at the end of the range, you can expand the range covered by the name. That's one of the reasons to use a named range so it always refers to a table of data no matter how large that table gets.

    Office Automation allows control, through VBA, of another Office product. It is not simple and this medium is not the place to go into a tutorial on it. Like I said there is plenty of code examples and instructions on the WEB to get you started.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2012-12-05T12:46:22+00:00

    Can't name the range as it will vary in the number of rows each day.

    How do I use Office Automation - do you mean create a macro?  Have used macros in excel before now, but how do I tell Access that I want to do something in Excel before importing data?????!

    Was this answer helpful?

    0 comments
  5. ScottGem 68,830 Reputation points Volunteer Moderator
    2012-12-05T12:37:05+00:00

    Can you have the person who creates the spreadsheet give a name to the range that contains the data? Then you can just import the named range.

    Other than that, you can use Office Automation to open the sheet, delete the first two rows and the row below the data. You can find examples of using Office Automation to edit a worksheet many places on the WEB

    Was this answer helpful?

    0 comments