Export to excel

[tkaplan]

I'm not sure if i should post this on the access forum or excel, so i'll do both.

I have a query that if i export the query in excel format, i am given the option of "save formatted". i need this option to be set to no.
if i program a macro in excel to export the file for me, is there a parameter that it will set the option to no, because the default it yes.

Thank you,
tkaplan

[Rembo]

Hello tkaplan,

I have a query that if i export the query in excel format, i am given the option of "save formatted".

Do you mean you export data from Access? The rest of your question below suggest you are making an export from Excel.

i need this option to be set to no.
if i program a macro in excel to export the file for me, is there a parameter that it will set the option to no, because the default it yes.

Could you provide the code of the routine that this concerns? It makes it a little easier to see what you are trying to do.

In general, if you are using a regular MS Access query from Excel the headers of your table (field names) will be formatted in bold style.

You could simply write a routine that unformats the data of the table header (field names). Well, actually it reformats the data in the table header.

Here's an example that removes the Bold style from data in range A1:E1

[VBA]Sub ReformatData()
ActiveSheet.Range("A1:E5").Font.Bold = False
End Sub[/VBA]

Rembo

[tkaplan]

I apologize for being unclear.
I am exporting from access to excel. i need to have the save formatted button checked because if not, it not only formats the look of the top row of cells, but if i have a column hidden, if the checkbox is checked it will not export. if i do it through the macro, it exports those columns as well.

[Rembo]

Hello tkaplan,

I am exporting from access to excel. i need to have the save formatted button checked because if not, it not only formats the look of the top row of cells, but if i have a column hidden, if the checkbox is checked it will not export. if i do it through the macro, it exports those columns as well.

It's still not very clear how you are exporting the data. Are you using a default "Send report to file" button on a form, or did you create a custom macro? If you like you can upload a sample database so I can have a look at it. It's probably easier to explain using a sample.

Remco

[tkaplan]

I will try to modify the database to something i can upload (there is currently a ton of confidential data in there).
Let me try to explain better though:
Until now, I was manually selecting the query, clicking file-export, and NOT selecting save formatted. So I want the user to be able to do this on their own. So I wrote a macro that exports the file for them. This by default saves it formatted. That's what I need to know how to change, either in code or the macro tab.

I hope this is more understandable now.
Thank you,
tkaplan

[Rembo]

Okidoki, now I understand what you are doing. This is actually more of a question for the Access forum but I might be able to help you anyway.

There are actually several ways to solve this but the easiest way is to use the build-in function 'TransferSpreadsheet':

Create a new macro -> TransferSpreadsheet and under properties define the specifics. In stead of a table name you fill in the name of your query.
As a worksheet type use Microsoft Excel 3.
Close the macro definition form and name your macro when Access asks you for it. If you succeeded you should now see your macro listed.

Now switch to your form and edit it. Add a button to the form header -> select Other -> Start macro -> button Next -> select your macro -> button Finish -> close the form and save your changes.

If all went well it should now work. Note that by selecting a Microsoft Excel 3 worksheet for export, I lost the format automatically.

Hope this helps,

Rembo

Until now, I was manually selecting the query, clicking file-export, and NOT selecting save formatted. So I want the user to be able to do this on their own. So I wrote a macro that exports the file for them. This by default saves it formatted. That's what I need to know how to change, either in code or the macro tab.