Solved

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

Posted on 2003-10-23
6
678 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now