Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 742
  • Last Modified:

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

0
mikskibum
Asked:
mikskibum
  • 3
  • 2
1 Solution
 
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.
0
 
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.

0
 
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
0
 
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"
0
 
jadedataMS Access Systems CreatorCommented:
mikskibum:  Thanx for the question!
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now