Convert txt to xls

[careybroph]

It's been YEARS since I've monkeyed in VBA, so I'm more than a little rusty! I have an MS Access program where I need to convert a tab delimited file to a true Excel file. So... I'm thinking, open Excel and record a macro, then copy the code into my program. I ended up with the code below, but it's not working (error: "Object Required"). I think it's because Excel was already open when I record the macro, but it's not open when running from th MS Access form. I feel like I am SO close! My code is below. Is their a special VBA command to open Excel (and I suppose another one to close it when I'm done)???

THANKS for any/all help!

Please Login or Register  to view this content.

[Leith Ross]

Hello careybroph,

Welcome to the Forum! Since you are running this from Access, you need to either have Excel open, add a reference to Excel in your Access project, or use late binding to create an instance of Excel.

The first option really isn't very practical. The second option is good if the program will always be running on the same computer, that is, the program will not be distributed. The third option allows for distribution, but may raise security flags.

Adding a Reference to your VBA Project
1) Open the Access file
2) Press ALT+F11 to open the Visual Basic Editor
3) Press ALT+T to display the Tools menu
4) Press R to display the References dialog
5) Scroll down the list to find Microsoft Excel x.x Object Library The x.x is the version.
6) Press the Space Bar to select or undo your choice.
7) Press Enter when you finish.
8) Save the reference by pressing Ctrl+S

If you are interested in the third option, late binding, let me know and I'll code the macro for you.

As a new member please the read the Forum rules for posting by clicking here

Sincerely,
Leith Ross

[careybroph]

I think the third option makes the most sense since multiple folks will be using the program. I should be able to manage the security problems, as I'm the system admin for our group. It's a small number of people who will be using it, so if I have to go with Option 2, I can probably make that work (I'm assuming I would have to open Access on each of these systems and add the reference to excel).

Thanks for your help!
ps. You referenced the 'rules'. Did I post something incorrectly? I think I may not have used the 'code tags'. (sorry!)

[careybroph]

I got it to work with option two. If you don't have time to show how to do option three, I'll be okay. It's a relatively small group of folks who will be using the app, and they are in my unit. I can document for them what to do if they have any problems. If you find the code that does it for option three, I can revise it later.

I'm still interested in knowing if I did something incorrectly in my original post (noticed your comment towards the end).

Thanks again for the help!!!
Scott

[Leith Ross]

Hello Scott,

Here is the code use late binding...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[ctmurray]

I think the third option makes the most sense since multiple folks will be using the program. I should be able to manage the security problems, as I'm the system admin for our group. It's a small number of people who will be using it, so if I have to go with Option 2, I can probably make that work (I'm assuming I would have to open Access on each of these systems and add the reference to excel).

Thanks for your help!
ps. You referenced the 'rules'. Did I post something incorrectly? I think I may not have used the 'code tags'. (sorry!)

To me (also a newbie) your post looked fine. I think they just point all newbies to the rules page.

Cam

[Leith Ross]

Hello Cam,

We assume all new members, because they are new, aren't familiar with the forum's rules. As a courtesy, we provide a link for viewing them. If you'll notice, I edited the first post by wrapping the code.

Sincerely,
Leith Ross

[ctmurray]

And the link to the rules page is appreciated.

I did not noticed you had intervened on the original post. Thanks for all the help this forum has provided me lately.

Cam