Specifying Column Width in Excel after using the TransferSpreadsheet Command in Access

I am using the DoCmd.Transfer Spreadsheet function in Access to output queries to Excel.  Is there a way to specify the column width in Excel from Access?  The problem is that I am using a new spreadsheet each time and so I can't just format the spreadsheet.  I need Access to pass the column width info to Excel.  Below is the command that I am using in a Module in Access.  Thanks for your help.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strSourceName, strFileName, True

mikskibumAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HaydnHCommented:
"The problem is that I am using a new spreadsheet each time and so I can't just format the spreadsheet."

You could create a blank spreadsheet and use the shell() or FileCopy() functions to copy your formatted spreadsheet to the new spreadsheet name, then move the data.

Haydn.
mikskibumAuthor Commented:
Haydn,

I know this is a pain, but I don't know much about the shell function.  Also, there may be different numbers of colums each time.  Would one of those functions be able to handle varying numbers of columns?  I appreciate the help.

jadedataMS Access Systems CreatorCommented:
Hey mikskibum!

  To do that you would have to have the workbook opened as an Excel Object.  
  If you do this you could bypass the TransferSpreadsheet command and use CopyFromRecordset to fill the workbook.
  but,.... this would give you the advantage of being able to fully format the worksheet in question to your most detailed specs.

  The VBA code for the question is
  xlobj.workbook(1).worksheets(1).range("C").entirecolumn.width = 15

  or
  xlobj.workbook(1).worksheets(1).range("C").entirecolumn.autofit

regards
Jack
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mikskibumAuthor Commented:
Jack, sorry for the delayed response.  I am using the following vba code and running it from a macro.  The query name is specified in that macro and passed to the code.  Do you know where in the code I could put your xlobj?  For the life of me I could not get it to work.  I like the idea of it autofitting.  Your help is much appreciated.  

Thanks,
Mike



Option Compare Database

Function TEST(strSourceName As String, _
      strFileName As String)

   Dim xlApp As Excel.Application
   Dim xlWrkbk As Excel.Workbook
   Dim xlChartObj As Excel.Chart
   Dim xlSourceRange As Excel.Range
   Dim KillFile As String

    KillFile = "c:\Template2.xls"
    If Len(Dir$(KillFile)) > 0 Then
    SetAttr KillFile, vbNormal
     Kill KillFile
    End If

   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
         strSourceName, strFileName, True
   Set xlApp = CreateObject("Excel.Application")
   Set xlWrkbk = xlApp.Workbooks.Open(strFileName)
   Set xlSourceRange = _
         xlWrkbk.Worksheets(1).Range("a1").CurrentRegion
   Set xlChartObj = xlApp.Charts.Add
   With xlChartObj
      .ChartType = xlLineMarkers
      .SetSourceData Source:=xlSourceRange, _
            PlotBy:=xlRows
      .Location WHERE:=xlLocationAsNewSheet
      .HasTitle = True
      With .ChartTitle
         .Characters.Text = "Top 10 query"
         .Font.Size = 18
      End With

        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Escalations"
      .HasLegend = True
      .Legend.Position = xlLegendPositionBottom
   
    .HasDataTable = False
 
   End With

   With xlWrkbk
      .Save
      .Close
   End With
   xlApp.Quit
   Call CallOpenExcel

Exit_TEST:
   Set xlSourceRange = Nothing
   Set xlColPoint = Nothing
   Set xlChartObj = Nothing
   Set xlWrkbk = Nothing
   Set xlApp = Nothing
   Exit Function
   
End Function
jadedataMS Access Systems CreatorCommented:
your xlApp is equivalent to my xlObj.

The above looks like it should be working ok,... You need to add error trapping to it to trace the source of unwanted "features"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jadedataMS Access Systems CreatorCommented:
mikskibum:  Thanx for the question!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.