Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-19-2017, 09:37 AM   #1
trackstar2786
Newly Registered User
 
Join Date: Sep 2017
Posts: 9
Thanks: 7
Thanked 0 Times in 0 Posts
trackstar2786 is on a distinguished road
Excel Formating on Docmd

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
Attached Images
File Type: jpg Scroll Before.JPG (11.7 KB, 25 views)
File Type: jpg Scroll After.JPG (11.5 KB, 24 views)

trackstar2786 is offline   Reply With Quote
Old 10-19-2017, 10:06 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 2,567
Thanks: 0
Thanked 543 Times in 529 Posts
Ranman256 will become famous soon enough
Re: Excel Formating on Docmd

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
Ranman256 is offline   Reply With Quote
Reply

Tags
docmd outputto , format excel worksheet , scrollbar position

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Kill Excel spreadsheet but keep formating Danick Modules & VBA 15 06-23-2017 04:31 AM
[SOLVED] Formating cells in Excel TimTDP Modules & VBA 3 08-19-2014 12:49 AM
Formating a column in excel Ammarhm General 1 10-23-2008 04:51 AM
Formating and Excel Worksheet Kenln Modules & VBA 2 11-06-2006 04:41 AM
formating excel columns from access Mcgrco Modules & VBA 4 08-11-2005 06:09 AM




All times are GMT -8. The time now is 11:30 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World