Export to Excel Button

[New.User]

I have created a form with Export to Excel button using simple macros. Right now the exported Excel column headers has a smaller font size and difficult to view. Editing font size in the excel sheet is definitely not an option. How do I specify the font size (or make it bold) before exporting the access form to excel?

Regards
New User

[JoeM]

What does the code behind your button look like?
It is usually better to export the Table or Query behind the Form than it is to try to export the Form itself.

[New.User]

Dear Joe,
Thank you for the response. It is a simple macro, no vba. I have attached the snip image of the vba.

[JoeM]

I think the problem is that you are using "ExportWithFormatting". So, it is going to export whatever formatting you have used in the Report.
Exporting Forms/Reports to Excel is not really the best solution, as those objects weren't really build for exporting.
Is it possible to export the underlying data source for this report instead? That would probably work much better.
Otherwise, you can try changing the font/size formatting on your Report.

[New.User]

Exporting form or report gives me the same result. What do you mean by " Is it possible to export the underlying data source for this report instead"?
Is it possible to write a VBA for the button instead of macros?

[JoeM]

What I mean is this:

Every Report and Form has a Record Source. This is either a Table or Query where the data comes from.
So what I am suggesting is to export this Table or Query that is used as the Record Source for your Report INSTEAD OF exporting your Report.

If you really want to try to export the Report instead (which is not really the recommended way of doing this, since Reports are really just meant for viewing and printing), then you will need to mess around with the formatting on the Report, as you chose the ExportWithFormatting action (note the "...WithFormatting" part - meaning if you want to change the format of what is being exported, you need to change the formatting right on the Access Report).

[New.User]

Ah. I understood and I take your advice but as I said the excel format must be obtained either from form or report. Forget about ExportWithFormatting macros for a while, is there a way through coding it?

[JoeM]

I understood and I take your advice but as I said the excel format must be obtained either from form or report.

Can you briefly explain why? I just want to make sure that we are on the same page and understanding each other.

What Font and Size are you using for the fields on your Report?
Have you tried changing those? That would probably be the easiest solution.

is there a way through coding it?

Well, the VBA code would look very similar to the Macro. As matter as fact, there is a utility within Access to convert your Macros to Visual Basic.
I don't really know that you can change the formatting of the Report as you export it. Even if you could, that would probably really mess up spacing, and it might look like a mess.
This is one of the reasons why people don't typically export reports to Excel.
You used to be able to export data with or without formatting, but I believe that only applies to Tables and Queries.

[New.User]

The requirement is from my supervisor. The excel sheet will be exported to an ERP system.
The font size for Field names in the form is 14 but the font size on the excel Header is 10. No matter how much I increase the Form field name font size there is no effect on the excel sheet.

[JoeM]

The requirement is from my supervisor. The excel sheet will be exported to an ERP system.

Is it able to be imported into the ERP system "as-is", meaning in the exact format that it was exported?
I ask because most programs I have come across want data to be in a simple, structured format (and not a Report-type format). To create import files, exporting from Tables and Queries typically works MUCH better.
And if it is just being imported into another system, that other system probably doesn't care about the font or size that the data is in.

Perhaps we should be coming about this in a different manner:
- What exactly does your Report format look like? Can you post a sample?
- What is the exact requirements from the ERP system regarding the structure of the import file?

One last thing:

The font size for Field names in the form is 14 but the font size on the excel Header is 10.

Which font is the Report using? I think some Fonts may export better than others.

[New.User]

The font size for form and report is the same. Unfortunately i cannot post the report
I have not yet tested the exporting of the the file to ERP yet but even to visualize the excel sheet it is not convenient. I can post a sample excel sheet image for you.

Can you see the Headers? They are so small.

[JoeM]

If you open your Report in Design View, are the fields holding the headers the same font and font size as the fields returning the data?

By the way, if this is how your data is really structured in your Report, it looks very similar to the structure of a Query or Table.
What exactly is it that the Report is providing that a Query would not?

If you are married to this idea of exporting this Report, here are a few options that you can consider:
- Create an Excel macro that fixes the formatting on the exported Report. The Excel macro can be called from Access right after the Report is exported.
- Export the data to a pre-defined Excel template, formatted the way you like. See this for details: https://social.msdn.microsoft.com/Fo...orum=accessdev

[New.User]

My supervisor does not want his users to go to the queries and tables. He just wants to play around with forms and reports only.
The font size for fields holding the headers in the access form or report is 14 where as the excel sheet column headers (fields) is 10.
I will look into the link you sent but i was searchin for some VBA code where in you specify the cell number in the excel sheet to be so and so font size. Is it possible?

[JoeM]

My supervisor does not want his users to go to the queries and tables. He just wants to play around with forms and reports only.

They wouldn't have to. The button can export it form the Table/Query, and they wouldn't know the difference. They do not need to go into the Table or Query at all. Just the Form containing the button.

I will look into the link you sent but i was searchin for some VBA code where in you specify the cell number in the excel sheet to be so and so font size. Is it possible?

Excel has a great little tool called "Macro Recorder". This works really well with formatting things like this.
All you have to do is export a test file, open it in Excel, go to the Macro menu and turn on the Macro Recorder, and record yourself making that font size change. Then stop the Macro Recorder.
Now, if you go into the VBA Editor and view the code you just recorded, you will have the VBA code you need to make this change.

[New.User]

Oh wow, i didnt know that i can record a macro in excel and use it in access. I would definitely like to give it a try. Thank you Joe.
But pardon me may be i am not so keen on going to tables or queries as my supervisor would not like it also i am not adept in explaining to him.