Excel Formatting

[SorenIX]

I placed your line before closing the function, but it's not saving it.

rst.Close
Set rst = Nothing
 
xlWBk.SaveAs CurrentProjectPath & "\" & outputFileName
xlWBk.Close True
ApXL.Quit

 

[SorenIX]

My bad there was a missing "." -> CurrentProject.Path.

Now its hooked to your code. I think everything is working. :banghead:

 

[SorenIX]

It's almost all working.

There are some fields that are truncated on a space.

So the field is "Field " and it in the "Translate" table, it'll be "Field" because it automaticaly trims the spaces at the end. Hence, it won't correct the name in the header of the exported file.

Is there a way to compare the truncatedTableField - 1 character to the fullTableField ?


Edit : I lied, this doesn't have anything to do with it. Certain names are just getting ignored by the comparision.

Edit : Maybe I lied about lying... I don't know anymore if it's the reason. Help? :banghead:

 

[SorenIX]

I'm going to assume it has something to do with the spaces, as the only fields that don't get corrected have spaces at the end in the table.

 

[SOS]

I'm not sure of the best way to handle that. I would probably benefit from an upload of a copy of the database.

 

[SorenIX]

As requested, here's what's needed for this case.

Hope you can find a nice solution other than changing the names in the table as there are many references to them.

Give me news! I'll be looking into as well.

 

[SOS]

I've just started looking but the very first thing I see, and wonder why you have this is this:

Public Function ExcelExportFunction(strTQName As String, Optional strSheetName As String)
' strTQName  is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to name it to
 
    strTQName = "TempExportTab"
    strSheetName = "Export"

You don't set the values like that. The declarations are in the procedure header so it is wanting you to pass those names in from the function call. And what is TempExportTab? The way this function works is that strTQName is the name of the table or query you want to export and I don't even see that one in your database.

The way you would call this function is

ExcelExportFunction "ExcelTab", ""Feuil1""

that is, if the table to be exported is named ExcelTab, which I assume from your sample and the Feuil1 would be what the worksheet would be named.

 

[SOS]

Next question - are you using a template for export? You modified the code to have this:
Set xlWSh = xlWBk.Worksheets("Feuil1")

That is not correct unless you were opening an existing Excel file and this code does not open an existing Excel file. In fact, you shouldn't have had to modify that SendTQ2Excel code at all (except to remove the formatting code). It was built for reusability and you have neutered it.

If you are using a template as a starting point, then you would need to use different code which he has on the site.
http://www.btabdevelopment.com/ts/tq2xlspecwspath

 

[SOS]

Make sure you read the two previous posts by me, but here's a third question -

Can you give me an example of one that has a problem with the space truncation issue? I'm trying to see and I can't find one.

 

[SorenIX]

I got nuked with questions.

You don't set the values like that. The declarations are in the procedure header so it is wanting you to pass those names in from the function call. And what is TempExportTab? The way this function works is that strTQName is the name of the table or query you want to export and I don't even see that one in your database.

The way you would call this function is


Code:
ExcelExportFunction "ExcelTab", ""Feuil1""
that is, if the table to be exported is named ExcelTab, which I assume from your sample and the Feuil1 would be what the worksheet would be named.

First of all, I guess I apologize for not knowing how to set values. As I maybe mentionned, I self taught VBA, so there are some basic steps I may have skipped in my learning.

Second, the TempExportTab is the table created from the ExcelTab (That query make table you mentionned on the first posts). You can see the code on the Export click button code, it creates the table with the desired columns.

Next question - are you using a template for export? You modified the code to have this:
Set xlWSh = xlWBk.Worksheets("Feuil1")

Third, I use Feuil1 because my office is in french. Whenever I put Sheet1, it created a bug. It picked on Feuil1, so I thought it was the right thing to do.

Can you give me an example of one that has a problem with the space truncation issue? I'm trying to see and I can't find one.

Last, the fields called "Criticality - Contribution ou Impacts Financiers / Contribution " and "Criticality - Contribution ou Impacts Réputation / Contribution " in the ExcelTab are the two that don't get picked up by the "Translate" table.


Hope it answers all of your questions.

 

[SorenIX]

Just refreshing the topic a bit, I still haven't found the solution to the final problem. :banghead:

After that it will be all solved! Yay!