DaFou
asked on
Import excel data
Ola,
How do I import data from excel having rowheaders but with the 1st row always empty. Thus having the rowheaders starting on row2
How do I import data from excel having rowheaders but with the 1st row always empty. Thus having the rowheaders starting on row2
Easiest way? Delete row 1.
ASKER
I meant using VBA. it is an automated process
So automate the deletion of row 1:
MyWorksheetObject.Rows(1). Delete
MyWorksheetObject.Rows(1).
ASKER
I am almost 100% sure that is I would do that in access VBA the code will not work.
If you want 500 points you should be more specific. maybe even try to give some code that works?
If you want 500 points you should be more specific. maybe even try to give some code that works?
>>> almost 100% sure
Really? Did you TRY IT? Because I tried the same line with a test worksheet, and it worked beautifully. I made one Excel worksheet with a blank row, followed by this data:
Row1: <blank>
Row2: head1 head2 head3
Row3: 1 2 3
Row4: 4 5 6
Row5: 7 8 9
When I ran this:
xlBook.Sheets(1).Rows(1).D elete
xlBook.Save
The worksheet's first row was deleted, and the file was saved. All completely automated, except for needing me to run the sub. I don't know how much more specific I can be without coming over and typing it for you.
Really? Did you TRY IT? Because I tried the same line with a test worksheet, and it worked beautifully. I made one Excel worksheet with a blank row, followed by this data:
Row1: <blank>
Row2: head1 head2 head3
Row3: 1 2 3
Row4: 4 5 6
Row5: 7 8 9
When I ran this:
xlBook.Sheets(1).Rows(1).D
xlBook.Save
The worksheet's first row was deleted, and the file was saved. All completely automated, except for needing me to run the sub. I don't know how much more specific I can be without coming over and typing it for you.
ASKER
if you read carefully you will see i write:
I am almost 100% sure that is I would do that in access VBA the code will not work.
I am sure it wont work becuase the excel object does not live from within my access VBA.
Perhaps you could give me the code that instantiates a specific excel object from within access 97. delete a row from it and safes it. That would be helping me.
But what you keep doing is telling me how to delete a row from within excel while I am most obviously talking about an access envirnoment.
I am almost 100% sure that is I would do that in access VBA the code will not work.
I am sure it wont work becuase the excel object does not live from within my access VBA.
Perhaps you could give me the code that instantiates a specific excel object from within access 97. delete a row from it and safes it. That would be helping me.
But what you keep doing is telling me how to delete a row from within excel while I am most obviously talking about an access envirnoment.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>I am sure it wont work becuase the excel object does not live from within my access VBA.
To reiterate on a point routinet made, you need to check your references. Go into any code window either by creating a new module, or double-clicking on an existing one. Then go to the Tools menu, References... item, and eyeball whether the 'Microsoft Excel {Some version} Object Library' is checked. If not, scroll down until you see it, then check the checkbox to the left of it, and hit Ok.
You will now be able to use the Excel VBA Object Library within Access.
To reiterate on a point routinet made, you need to check your references. Go into any code window either by creating a new module, or double-clicking on an existing one. Then go to the Tools menu, References... item, and eyeball whether the 'Microsoft Excel {Some version} Object Library' is checked. If not, scroll down until you see it, then check the checkbox to the left of it, and hit Ok.
You will now be able to use the Excel VBA Object Library within Access.
ASKER
Let me please explain why all this misunderstanding began in the first place.
After a while routinet suggest to use this peice of code:
MyWorksheetObject.Rows(1). Delete
Now I knew ( while i said out of politeness: almost sertain ) that this would not work becuase of the fact that there is no variable living in my code yet that goes by the name of MyWorksheetObject
I'll admit that I am versed enough to figure out how to create a variable to such extend but as I marked this question as 500 points ( in my case - urgent, not difficult )
I expected a more copy past type of comment.
I went over the top to actually get uncomfortable with that and for that I apologise and I also beleive the routinet went over the top to incorperate humor in his definitife and correct answer.
Once again Much apologies for all this.
Regards
After a while routinet suggest to use this peice of code:
MyWorksheetObject.Rows(1).
Now I knew ( while i said out of politeness: almost sertain ) that this would not work becuase of the fact that there is no variable living in my code yet that goes by the name of MyWorksheetObject
I'll admit that I am versed enough to figure out how to create a variable to such extend but as I marked this question as 500 points ( in my case - urgent, not difficult )
I expected a more copy past type of comment.
I went over the top to actually get uncomfortable with that and for that I apologise and I also beleive the routinet went over the top to incorperate humor in his definitife and correct answer.
Once again Much apologies for all this.
Regards