Solved

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

Posted on 2003-10-23
6
698 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Record with #Error 8 33
How open a file when clicking on a text field 7 20
Access Schema 6 26
Back-end Database connect string being set someplace where I can't find it. 10 28
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

828 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