BusinessObjects Board

how to auto 'size' the columns in Excel in vba

sdk development
#1

We are exporting our data from the report to excel via vba, then sending that excel file off to Outlook, which in turn, sends it off to our vendors.

How do I (after creating the excel file via vba) auto fit or auto size or whatever excel calls it to make every column the width of the contents so that the receiver of this information doesn’t need to expand the cells to see it?

Anyone know? Dave?

BusObjGuy :us: (BOB member since 2002-10-17)

#2

Try something along the lines of

    Cells.Select
    Selection.Columns.AutoFit

That’s the Excel side of it, anyway. Good luck! :slight_smile:

JennFisher :us: (BOB member since 2002-06-25)

#3

In my code, I have to use the ConvertTo function to convert the report data to excel.

At which point, it is saved to a file once I specify a path.

I think then, I have to reopen the file, auto fit the cells, then save it as the same name again. Then attach it in the email.

How do I re-open the excel file in vba or can I write code to do this as I export so how?

BusObjGuy :us: (BOB member since 2002-10-17)

#4

You’ll need to add the Excel object library to the references in your macro (Select Tools + References and check the Excel option). From that point you can actually start Excel in the background and drive it from BusinessObjects. Tell it to open the file, apply the logic that Jenn gave you earlier, save and close the file.

It’s not difficult, but you do have to have Excel installed and available. If this macro is to be run on a server, that may be a bit of an issue. Some folks don’t like to have apps running on their servers. :slight_smile:

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)

#5

To run applications on a server, for things like opening and reformatting a spreadsheet, or sending email from Outlook, do you need to have a user permanently logged into the server? Or does it depend on what you’re trying to do?

Paul.

Paul Williams :uk: (BOB member since 2002-07-10)

#6

We have both. We have an NT admin account setup that is always running the pc. It has excel on the server because we had to send out Excel spreadsheets via email, which means it has Outlook installed too. I have the dll’s registered already for both outlook and Excel. I just have never written the statements for Excel to open the file, autofit and re-save. Wondered what the syntax.

Our box doesn’t get used much and that is all that is on the box for our use. It is dedicated and no other use has been designated on the box.

BusObjGuy :us: (BOB member since 2002-10-17)

#7

Check the code samples library for a piece of code that does what you want. You can add the resize call to that code and you should be all set…

avaksi :us: (BOB member since 2002-08-22)

#8

Thanks, I think this was what I was looking for.

BusObjGuy :us: (BOB member since 2002-10-17)

#9

Ok, so I finally got around to working with this, and my main problem is still not working with the suggestions below to incorporate the :

Cells.Select
Selection.Columns.Autofit

It is almost like it doesn’t recognize the range of columns or rows. This code, once I email it or put the excel file on my hard drive, makes this xls file unusable. I implemented it using most of the code that was from the code samples. I believe I understand the code pretty well that was written for downloading BO reports to Excel spreadsheets.

So don’t you have to put something in here to specify what range of columns or cells?

Any suggestions would be very appreciated.

BusObjGuy :us: (BOB member since 2002-10-17)

#10

I have found the code from the code samples that deletes the worksheets 1, 2, 3 from Excel when converting the BO reports to Excel using VBA, but I see it is commented out. I get it to work, if I answer the EXCEL dialog box that pops up (because I allow interaction) saying “Am I sure I want to delete the sheet requested?” when running my vba script.

How do I answer that box with BCA, without interaction from the user. Essentially, how do I set the dialog box answer to Yes or OK, so that it deletes sheet 1,2,3 before I save the workbook in VBA, using code instead of interaction from someone?

Application.Interactive = False only works for BO functions, not Excel dialog boxes.

Any thoughts?

BusObjGuy :us: (BOB member since 2002-10-17)

#11

You can use the following to select all of the “used” cells on a page (if you don’t want to just use Cells.Select. It’s useful for counting the number of rows used, etc. Also note… if you are still having issues, make sure that the Worksheet you want to select is activated first (as in the following example):


Worksheets(1).Activate
ActiveSheet.UsedRange.Select

To remove the “Prompt” for deletion, turn it off in Excel using:


Application.DisplayAlerts = False
For Each wksht In Worksheets
    wksht.Delete
Next wksht
Application.DisplayAlerts = True

Let us know if it works.

-RM

digpen :us: (BOB member since 2002-08-15)

#12

I will give this a try. When you turn the Alerts off, does it answer it as the default? If it doesn’t present the question to begin with, then does it just do the action requested?

BusObjGuy :us: (BOB member since 2002-10-17)

#13

If it doesn’t not alert, it will just perform the action without asking for confirmation.
-RM

digpen :us: (BOB member since 2002-08-15)