Solved

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

Posted on 2003-10-23
6
721 Views
Last Modified: 2011-10-03
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

0
Comment
Question by:mikskibum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:HaydnH
ID: 9607305
"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.
0
 

Author Comment

by:mikskibum
ID: 9607327
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.

0
 
LVL 32

Expert Comment

by:jadedata
ID: 9607345
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mikskibum
ID: 9614610
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
0
 
LVL 32

Accepted Solution

by:
jadedata earned 150 total points
ID: 9614812
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"
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9633800
mikskibum:  Thanx for the question!
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question