Append and Column Names, linked excel file

pdvsa
pdvsa used Ask the Experts™
on
Experts,

I have this excel spreadsheet that is quite large.
I would like to be able to link to this excel spreadsheet.
The problem is that there is a lot of garbage above the actual column names which makes linking and appending data to this excel file difficult.  

How can I link to an excel spreadsheet that would allow me to Append data to it taking into account there is a lot of extra stuff between row 1 and the row that contains the actual headers?  I thought a possible solution might be assigning a named range to the row that contains the column names but I am not sure if this strategy will allow to APPEND data to the last row of data.  

Also, under a linked excel file setup, does Access APPEND to the last row of data or do you have to have extra code for this?

I  have never done something like this and am looking for some tips.  
I can not edit the excel file...meaning the extra garbage will have to stay there.

thank you
(attached picture of excel file wanting to link to.  Notice rows 1-5)
untitled.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Rather than linking to the file, which would admittedly be the easier method, I think your best choice would be to create an unbound form, and use Automation to Open the Excel file in the background and read the data into your form the hard way (one record/field at a time), if you need to edit it or add to it.

This will be a little more difficult than linking and creating a bound form, but it is not too difficult.  Are there only these 13 columns, or are there more?
pdvsaProject finance

Author

Commented:
fyed: thanks for the response.

<Are there only these 13 columns, or are there more?
yes, there are many many more...about 40

I do have a form that exports to this excel file but it seems not to work as well I I would like and now I am thinking to just link to the file and append to it.

<create an unbound form, and use Automation to Open the Excel file in the background and read the data into your form the hard way (one record/field at a time), if you need to edit it or add to it.
==>Wouldnt I need to have a form that would have to have the same number of fields as number of columns?  I am not quite following this one.  My thinking is that the form would need to be bound.  
I'm not particularly familiar with the "append" feature, but I know any time you import data, the wizard gives the you option to choose the import cell. Is it possible for you to write a macro and then replace the default input cell ("a1") with range ("a6000").end(xlup).offset(1,0) ? This phrase dynamically chooses the last cell in a column that is not filled in. Then you could link the macro to a button and simply choose a text file in a dialogue.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
pdvsa,

Are you going to need to edit data that already exists?

Unbound forms.  This just means that the controls on the form are not bound to any record anywhere.  Yes, you would still need separate controls for each field in your Excel spreadsheet.  You can still create textboxes, combos, ... but you just don't set their Control Source.

What this means is that you have to write code to read the data from the individual cells of the Excel spreadsheet and put those values in the appropriate control of the unbound form.  Then, when you want to save the record, you have to also have code that will individually write the values from those fields back to the appropriate Cell(row,column) in Excel.  This is tedious with large numbers of controls, but really isn't too difficult.
pdvsaProject finance

Author

Commented:
fyed:
<Are you going to need to edit data that already exists?
No edit...only append.

I see it can be done but is tedious.  I have my answer.  thank you.   I dont know if you have an example but if you did and can post that would be great.  No worries if not...:)

SafetyFish:
<and then replace the default input cell ("a1") with range ("a6000").end(xlup).offset(1,0) ? This phrase dynamically chooses the last cell in a column that is not filled in.
I am not sure how well that would work because I do have blank rows then it starts again and such with rows of data and there are separate tables at teh bottom of excel.  


Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
pdvsa,

Don't have any examples handy, but if you search on Excel Automation here in EE, you should finds lots of examples of how to open Excel in the background and manipulate Excel.
pdvsaProject finance

Author

Commented:
thank you...
The .end(xlup) method is exactly like  you have a blank cell beneath your data selected and hit end+arrow up. It jumps to the first populated cell. The offset method puts it back down one row, grabbing the first blank cell. You could offset it by 2 or 3 rows if you intentionally wanted to put breaks in the data.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial