Exporting to Excel via Navigation Form

[KasperBN]

I have decided to move away from Switchboard in Access 2010, to what I find to be more user-friendly; the Navigation Forms.

However, when I attempt to export any of the data from my navigation forms, the result is a blank Excel sheet. I can easily go into the forms themselves on the left navigation panel and export from there, but when myself or a user attempts to do this from the navigation forms themselves; well, the blank Excel sheet is the result.

How do you create a work-around, so that it is possible to export to Excel from these navigation forms?

I am fairly new to Access 2010.

Thank you in advance!

 

[KasperBN]

Is anyone able to help me with this?

 

[KasperBN]

I can post more details or screenshots if needed

 

[KasperBN]

Any help would be appreciated.

 

[pwbrown]

Some more details would help and possibly a screenshot.
If you convert you database to access 2007 and upload it, it would help to get a better understanding of what your doing.

 

[KasperBN]

I have attached some screenshots and tried to upload the database, but it seemed a security token was missing(?)

Let me know if you need it, I will try again.

Basically what I would like to do is just be able to import to Excel from the navigation form. I am not sure if it's because it is only a link to the actual form, or how I can manage to do this, but as you can see, I merely get a blank Excel worksheet if I try.

 

[pwbrown]

Looked it up online and it seems navigation forms are something new added to 2010 so I probably won't be able to help, sorry.

 

[KasperBN]

Thank you anyways, I hope someone else will come along. And yes, I believe they are something that have been added to 2010. I just havent found a solution online for it.

I used to run a switchboard in 2007 where you could macro around it, but with navigation forms I don't know.

 

[pr2-eugin]

I hope you are trying to export is the Data behind the Form right? Why not export the Query rather than the Form?

 

[KasperBN]

Hi pr2-eugin, as mentioned I am pretty new to databases and Access and most of what I do is trial and error and Google

How do I go about exporting the Query rather than the Form? As it seems the navigation Form can't be exported.

(This is me attempting to make exporting for the users of the database as simple as possible).

 

[pr2-eugin]

So create a button that would initiates the Transfer, then use the DoCmd.TransferSpreadSheet method...

 

[KasperBN]

Bear over with me and consider me an access rookie

Would I create a button running a query? The link you sent me to is fairly confusing to me.

 

[pr2-eugin]

No no create a Button (name it exportBtn), the code behind will have the code..

Private Sub exportBtn_Click()
    DoCmd.TransferSpreadSheet acExport, [B]acSpreadsheetTypeExcel9, [/B]"theTableOrQueryName", "pathToSave"
[COLOR=Green]'If you have more tables to export.. 
'    DoCmd.TransferSpreadSheet acExport, [B]acSpreadsheetTypeExcel9, [/B]"someOtherQueryName", "pathToSave"
'    DoCmd.TransferSpreadSheet acExport, [B]acSpreadsheetTypeExcel9, [/B]"someOtherTableName", "pathToSave"[/COLOR]
End Sub

 

[KasperBN]

I apologize for struggling with something that should probably be very simple.

I have not made any queries in my database, only forms.

I have now created the exportBtn button, and added the code behind it you pasted.

When you post the table or query name, is it the name of the Form i should put in the code?

And should I put in anything on pathtoSave, or leave it as it is?

Again I apologize for being a tough user to teach

 

[pr2-eugin]

only forms.

These Forms will get their RecordSource from Some Table/Query right? That will be placed under someOtherTableName...

I have now created the exportBtn button, and added the code behind it you pasted.

Good..

When you post the table or query name, is it the name of the Form i should put in the code?

As mentioned at the beginning of this post..

And should I put in anything on pathtoSave, or leave it as it is?

The path to save will be the path you wish to export to.. Something like..

C:\Users\Paul\Documents\MyExportFolder\21-03-2013\

 

[KasperBN]

Private Sub exportBtn_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Products", "J:\Backup\"
End Sub


This is what my "test code" on my button looks like. However when I click it under my navigation form, nothing happens, whatsoever.

Should I put it under the original form, or under the navigation form? (does it even matter?)

I can try uploading a screenshot of what im attempting to do, but it seems if I put the button under the original form, then it does not show up in the navigation form. If I put it under the Navigation Form (in design view), then it shows up on every 'tab', and when I attempt to click the button under one of the tabs I wish to export, nothing happens.

Ill try and upload an SS so you can see if I am making a mess of it.

 

[KasperBN]

I am probably making a mess of something, but I cannot seem to see what...

 

[pr2-eugin]

You have to include a FileName.. I think that would have been my fault.. Try this..

Private Sub exportBtn_Click()
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Products", "J:\Backup\[COLOR=Red][B]Products.xls[/B][/COLOR]"
End Sub

In regards with the button appearing in all tabs, make sure that the proper tab is selected before pasting the button..

 

[KasperBN]

Wow this is frustrating.

I have added the filename to the code, and nothing happens, still. In regards to the button appearing in all tabs, I have selected the Products tab before creating the button (under design view). Yet it still shows up on the other tabs.

Am I creating the button wrongly...somehow, if that is even possible? It may have something to do with it showing up on all tabs, that I am not able to get it to export.

I have also attempting naming it xlsx since I use 2007. Neither one works.

 

[pr2-eugin]

Is it possible if you could upload a Dummy version of your DB for us to look into?? Check if the command has done its part in J:/Backup folder..