Access 2007 - VBA to Select External Data Ribbon

[Cele Balser]

Can someone tell me the VBA code to use in my module to select the External Data ribbon? I have a module that runs through some queries and at the end of them I want to export the query to Excel. I would like to show the Ext. Data ribbon to manually (or by code) select "Saved Exports" (I'll need to input a new filename each time). The line of code would be much appreciated. And would it matter if I did that before or after Setting Warning back to true?

Thanks much!
Cele

[Megalog]

I'm not entirely sure what you're asking for here.. Do you wish to simply pull up the Saved Exports menu programmatically?

Application.RunCommand acCmdSavedExports

If you need to actually load a new ribbon, or move to a different grouping within your existing ribbon, let me know.

[Cele Balser]

That is perfect! Exactly what I wanted to do!! Thanks a bunch!
Cele

[Megalog]

An easy fix after all =)

Welcome to Bytes!

[Zach85]

Is there a way to just have a saved export command run at the click of a button, or can I only just have it open the saved exports application and I still have to click on the export command I want it to?

[Megalog]

If you know the name of the saved import/export, and dont want any prompting whatsoever, you can use the following:

docmd.RunSavedImportExport "YourSavedImportorExportName"

[Zach85]

Thanks

Another question... since I'll be giving this to the guys I'm doing this for, is there a way to not have the longer filepath, but rather just look for, or export to, a file in the same folder? I tried to go (from my html background) with just putting in the file name, but that doesn't work, it just thinks to look for C:\filename.mdb

Thanks again so much for your help!

[Megalog]

Just to clarify what you're asking, you want your export to show up in the same folder that the database itself is currently in?

I believe if you use a saved export, you're locking in all the variables.. filename, path, etc. If you need to change this to add in some path detection, then you'll want to be using the docmd.OutputTo command, and then adding in the correct criteria behind that.

[Zach85]

I'll try to explain my problem:

Let's say I have the Access 2007 database in the following folder:

C:\database\

Right now, when I went through the export wizard to save the export, it asked me where to export to and I put in:

C:\database\file.mdb

Now if I were to take this database and put it on my external hard drive and take it to another computer and tried to run the saved export command, it'll give me an error, something like:

"C:\database\file.mdb is not a valid filepath"

I want it to export to a file in the SAME folder as the 2007 database file. But, my problem is that the filepath in the saved export is always static. I tried just typing the file name, assuming it would look first in the same folder if I did that instead of following a path. that didn't worked, it just looked for C:\file.mdb. Is there a way, like in html where you just type in "page.html" into the "a href" command instead of having to type the whole url in?

sorry if that does not made sense, but I think that's the best I can do. Thanks

[Zach85]

I got it to work by just messing around with it.

After the wizard to save the export, I went into the Saved export application and changed the file path to "./filename.mdb" which worked.

Thank you so much for your help on this, and the attempted help of the last problem I had. I sincerely appreciate it!

[Zach85]

it only works when you first do that. Then when you close the 2007 database and open it back up and try exporting it with that ".\filename.mdb" it just looks in the my document folder for some reason. I'll just use the long filepath then, it doesn't seem to be any other way.

Thanks again.

[Megalog]

Here's one way to do it:

Private Sub cmdExport_Click()
 
Dim strDB As String
Dim lngPath As Long
Dim lngRev As Long
Dim strLeft As String
 
strDB = Application.CurrentDb.Name
lngPath = Len(strDB)
lngRev = InStrRev(strDB, "\")
strLeft = Left(strDB, lngPath - (lngPath - lngRev))
 
DoCmd.OutputTo acOutputTable, "listview", acFormatTXT, strLeft & "YourExport.txt"
 
End Sub

You'll need to change the OutputTo variables to fit your situation (I'm exporting a table to a text file in my example), but it will place it wherever your database resides.

[Zach85]

I can't use the acFormat because there's no way to format it to mdb. I need to export it to filename.mdb, can't export it to txt,xls or any of the other acFormat extenstions that are supported.

[Megalog]

? What are you trying to do? Save a copy of the current 2007 database to the same folder as 2003 format? Please explain fully what it is you're trying to export.

[Zach85]

ok, here goes...

I started this database of contact people for my internship supervisor. I have the file saved as "contact list.accdb" which is the access 2007 extension. I wanted to import that into a marketing program I found, Email Marketing Program. It doesn't support the accdb extension (yet) and so I want to export the accdb format to the mdb format (Access 2003) in order to import it to EMP.

I tried using Save as, but that saves the WHOLE accdb program, when I just want to export the contact list (which is one of like 5 or 6 components of the accdb program). it will not open properly in the mdb extension because some of the stuff I'm using in the 2007 is new in 2007 and will not open in 2003.

Does that make better sense? Sorry if I wasn't clear previously.

If I have to, I could try exporting to txt comma delimited, but I'd much rather just keep it simple with two separate access files, one in 2003 and one in 2007. Thanks again.

I have to go now so I won't be able to respond for most of the day. I'll let you know how things go later tonight or tomorrow when I get back to working on my internship stuff. Thanks!

[Megalog]

So you basically want an .mdb, that holds only the tables with your contact information.. If you're using multi-value fields, or anything else specific to 2007, then I would suggest you either export to a comma delimited text file, only exporting the fields you need. Or, create an mdb, and then have your 2007 db run an update query on the mdb whenever your contact data gets updated. This would require you to link tables from the mdb to the accdb, and then dynamically relink when the files move around. Is there an advantage to using an mdb file, instead of a cst file, with this EMP program?

[Zach85]

Good point. I'm putting some contacts into the database right now from this one league. When I'm done this league, I'll see about exporting to a csv prior to starting on another league. Thanks!

Sorry for the delay in response, I had a rough weekend. Thanks again!

[Zach85]

Ok. I got it to work how you programmed it. It looks like a table in a text file, and I got confused. But then I realized that the acOutput is to a Table. Is there a way to have it export it as csv? This way it'll be able to read in the EMP? I don't think it will read as a table, but I won't be able to figure that out at the moment since I am in the computer lab working on this. I will be leaving her shortly to go back to my room, where I can test it to see how it turns out. If I can get it to do csv or some other delimited (i.e. tab) that would be great and much easier on my supervisors.

[Zach85]

Ok, I thought about it and I don't think the output way will work unless there is a specific way to export it to CSV.

Then, I thought, is there a way to save as, and then do the same way you did it with the output, by setting the strings and then having it like leftStr & "contacts.mdb" or whatever?

I then found acCmdSaveAs in the command library, but don't know how to use it and tried googling it but to no success. Think you could help me? This might make it work easier and this way I could just save it or export it as a mdb for better luck. Thanks in advance

[Megalog]

Sorry I was out monday. The command you'll want for this is DoCmd.TransferText. Do a manual export, and save any settings you want to a new Export Specification. Then, you can call upon that specification in your TransferText command as well, for example:

DoCmd.TransferText  acExportDelim,"SavedSpecificationName(Optional)","YourTableorQueryName","C:\Data\Exported Contacts.csv"

Now the filename portion can be built beforehand, using a bit of that code I posted earlier to build your db location string. Then you can add on a filename, and timestamp it as well. Or.. just overwrite whatever is existing in that current location if you want a static filename. Hit the help file on TransferText, and if you need any suggestions just ask away.

-Mega

[Zach85]

You, my friend, are a genius! It worked with the following:

Dim strDB As String
Dim lngPath As Long
Dim lngRev As Long
Dim strLeft As String

strDB = Application.CurrentDb.Name
lngPath = Len(strDB)
lngRev = InStrRev(strDB, "\")
strLeft = Left(strDB, lngPath - (lngPath - lngRev))

DoCmd.TransferText acExportDelim, , "Contacts", strLeft & "contacts.csv"

I didn't use the specification name, only because I don't know what that is. But like you said, it is optional and this works without it!

THANK YOU THANK YOU THANK YOU SOOOO MUCH! This certainly will reduce the headaches for my supervisors when I am done my internship and give them the program to use.

Thanks a lot, I really do appreciate it. I hope I wasn't bothersome haha, but this meant a whole lot to get something like this done. I really should learn VB now, since I plan to use access and excel a lot in the future. VB is very helpful!

[Megalog]

Good to see it all come together and work, eh? Glad you got your problem fixed.. and yes, vb is great.. you'll definitely find many uses for it in Excel as well as Access.

BTW, in the future, please put code tags around any vb you post. It's the '#' button in the posting editor.