• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

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

0
DaFou
Asked:
DaFou
  • 4
  • 4
1 Solution
 
Steve BinkCommented:
Easiest way?  Delete row 1.
0
 
DaFouAuthor Commented:
I meant using VBA. it is an automated process
0
 
Steve BinkCommented:
So automate the deletion of row 1:

MyWorksheetObject.Rows(1).Delete
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DaFouAuthor Commented:
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?
0
 
Steve BinkCommented:
>>> 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).Delete
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.
0
 
DaFouAuthor Commented:
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.


 
 
0
 
Steve BinkCommented:
Also, please remember that this code did not do anything when I ran it in Access 2002...maybe it will do something in Access 97.

Public Sub DeleteFirstRow()
Dim xlApp As Object
Dim xlBook As Excel.Workbook

' Basic error handling for creating the Excel object.
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
    Err.Clear
    Set xlApp = CreateObject("Excel.Application")
    If Err.Number <> 0 Then
        MsgBox "There was an unknown error while attempting to open Excel."
        xlApp.Quit
        Set xlApp = Nothing
        Exit Sub
    End If
End If

Set xlBook = xlApp.Workbooks.Open("c:\MyPath\test.xls")  
xlBook.Sheets(1).Rows(1).Delete                 '<--- DOES THIS LOOK FAMILIAR?!?!?!?
xlBook.Save                                               '<--- HOW ABOUT THIS!?

Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub

<sniff>  I liked that dog...
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
 
DaFouAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now