Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Import excel data

Posted on 2005-05-02
9
Medium Priority
?
402 Views
Last Modified: 2012-05-07
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
Comment
Question by:DaFou
  • 4
  • 4
9 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13909297
Easiest way?  Delete row 1.
0
 
LVL 2

Author Comment

by:DaFou
ID: 13910480
I meant using VBA. it is an automated process
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13910969
So automate the deletion of row 1:

MyWorksheetObject.Rows(1).Delete
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Author Comment

by:DaFou
ID: 13915549
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13918327
>>> 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
 
LVL 2

Author Comment

by:DaFou
ID: 13918831
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
 
LVL 51

Accepted Solution

by:
Steve Bink earned 2000 total points
ID: 13919279
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13969339
>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
 
LVL 2

Author Comment

by:DaFou
ID: 14078183
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question