2010 - Exporting to Excel via Navigation Form (blank reports)

[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!

 

[pr2-eugin]

How are you performing the Export KasperBN?

 

[KasperBN]

I am merely going to the navigation form which I want to export and Export to Excel Spreadsheet, as I would do in any other table. However when used via the navigation forms, this does not work.

 

[pr2-eugin]

Because the Form you are referring is still the Main Form (Navigation Form) and not the SubForm (whichever you want to export)..

So to do this, you might consider doing a bit of VB coding.. Not much fancy.. Create a button on the Form which you want to Export.. Then use..

Private Sub buttonName_Click()
    [URL="http://msdn.microsoft.com/en-us/library/office/ff192065.aspx"]DoCmd.OutputTo [/URL]acOutputForm, "[COLOR=Blue]yourFormName[/COLOR]", acFormatXLS, "M:\Output\Path\toTheDocument.xls"
End Sub

 

[KasperBN]

Silly question, now that i've created the button and attempt to click it, my database seems to crash every time (Not responding). The excel is imported to the specified folder, but is corrupted (probably because of the crash to Access I would assume?)

Is this merely because of a slow CPU you reckon?

 

[pr2-eugin]

Hmmm that is strange.. Did you try a Compact & Repair? Compile? Does the path you are trying to specify exist? Can you show the code you have?

 

[KasperBN]

A compact and repair fixed the crash issue, but not the validation / corruption issue. Here is the code (it is Excel 2010)

Option Compare Database


Private Sub Command508_Click()
DoCmd.OutputTo acOutputForm, "Products", acFormatXLS, "J:\Backup\toTheDocument.xlsx"
End Sub

 

[pr2-eugin]

There is the problem you are saving the file as XLSX.. so change the format as..

Private Sub Command508_Click()
         DoCmd.OutputTo acOutputForm, "Products", [B]acFormatXLSX[/B], "J:\Backup\toTheDocument.xlsx"
End Sub

 

[KasperBN]

You are a genius, thank you! Now I just need to figure out how to create an individual button for the subforms...

If I create a button in design view for one of the sub_forms, it replicates this button for all the other sub_forms under the main navigation form.

If it makes sense?

 

[pr2-eugin]

Yea I think I have seen this problem before.. Try creating the button on the subForm's individual design.. That will avoid it showing up on all the available Subform's..

 

[KasperBN]

Can't get it to show up if I do it this way, since I can't put it in the 'detail' section of the design view. If I try to put it under Form Header or Form footer, it just refuses to show up, not sure if it's being hidden behind the form or what.

If it's confusing I can link some screenshots of what I mean.

 

[pr2-eugin]

No I do understand.. Is it possible if you could attach a DUMMY version of your DB, that I could play around with?

 

[KasperBN]

Sure, ill see if I can get one uploaded quickly before I leave from work.

 

[KasperBN]

Hmm i'm getting a

Your submission could not be processed because a security token was missing.

If this occurred unexpectedly, please inform the administrator and describe the action you performed before you received this error.

The dummy is around 46k KB in compressed state, and im not sure im allowed to upload that much as a "newer" user, and not sure how i can compress it more, only have 2 subforms with one record in each.

 

[pr2-eugin]

Hmmm.. Not sure, what could cause the error.. Can you add it to DropBox or something..

(Or) Try ZIP - ing the file (Or) upload the raw version.. (Or) Try clearing your browser cache and try again..

 

[KasperBN]

I have tried both the zip and raw, no luck. I have added it to dropbox however.

Not sure, but do I need your email or does this clipboard link work?

https://www.dropbox.com/s/eh8gtggb2f6p2sb/Database.accdb

 

[pr2-eugin]

Hey Kasper, sorry could not get back to you yesterday... The problem with your attachment, it was 46 MB.. the site allows only 2MB max.. Note for next time.. Perform a Compact & Repair and upload...

Well the problem was you had the Form set up as Datasheet.. which cannot have buttons on them.. So I changed the view to continuous.. (I did not do a great job at it, as I have to do some other stuff at work) you will get the idea.. A Continuous form can be made to Look Like a Datasheet if formatted properly.. So I have amended the DB.. Check it and let me know how it goes..

 

[KasperBN]

Hmm how would I go about formatting it properly? As you can see it has to look like a datasheet otherwise it will get very...messy!

 

[pr2-eugin]

Am so sorry, I would love to help you, but today have some other work to deal with, and am afraid I will not be able to..

When I said Like datasheet it has its limitation.. You have to arrange them properly.. set the back colors.. Or the other cheeky way.. Not sure if it will be very efficient, do not change the format to Continuous, just make that (Products form) a SubForm inside a Normal form (say MainFrm_Products), then add this to Navigation form..

 

[KasperBN]

Finally had a chance to get back to you. I am still unsure how to make the Continous look like a Datasheet (which is important, seeing as you have to be able to easily sort data etc), and the other problem I have, is the error I get when opening the exported Excel sheet.

Excel cannot open the file "tothedocument.xlsx" because the file format or file extension is not valid. Verify that the file has not been corrupted...."

I have tried to change it to xls, but the Excel format we have is 2010.

Regards Kasper