Append and Column Names, linked excel file
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
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.
ASKER
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
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.
<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
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.
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.
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.
ASKER
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.
ASKER
<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.