Excel Formating on Docmd (1 Viewer)

trackstar2786 · Oct 19, 2017

I'm wondering if I can do some editing to an excel file either during one of my docmd modules or if this is something I have to do afterwards.

Module 1: Docmd.outputto

Runs query and outputs to excel (xlsx) with a specific file naming convention "AccountNumber_AccountName_Month_Year".
In terms of scope, I'm creating over 200 unique reports by account number
As an FYI, when checking the file after Module 1, the scroll bar is not covering any tabs

Module 2: Docmd.TransfterSpreadsheet

Runs query and adds a new tab called "Details" to the excel file created in Module one based on that specific account number

Where I need Help
Upon completion, I get the first attached image 'Scoll Bar before' jgp which covers the Details tab.

Is there anyway to get the file to move the scroll bar so that it's not covering up the 2nd tab. This will avoid the question of, 'Where's the details?' which I have to respond by telling them to move the bar over to the right as in the 'Scroll Bar After' jpg

Ranman256 · Oct 19, 2017

after the export, Docmd.Transferspreasheet... vFile
you must take control of excel and make it adjust...

Code:

   '///NOTE : YOU MUST ADD excel object library IN THE VBE menu, tools, references
Public Sub PostData2XL()
Dim XL As Excel.Application
Dim vFile

vFile = "c:\folder\MySpreadsheet.xls"

docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,vQry,vFile,true,vSheetName

Set XL = CreateObject("excel.application")
With XL
   .Visible = True
   .Workbooks.Open vFile
   .sheets(vSheetName).select

   .ActiveWindow.TabRatio = 0.258

   .ActiveWorkbook.Save
End With
Set XL = Nothing
End Sub

Excel Formating on Docmd (1 Viewer)

trackstar2786

Registered User.

Attachments

Ranman256

Well-known member

Similar threads

Users who are viewing this thread