Using OUTPUT TO in VBA, then how to modify the XLS from Access?

I'm using the simple "OUTPUTTO" command to send a query over to Excel, 
however we will have many users and we would like to programmatically add the 
header rows, date printed, turn on the auto filter and freeze panes, all of 
which I can do within a Macro in Excel, but how do I call that macro to run 
from Access and How to run it on the file that they just created?

Current Code:

DoCmd.OutputTo acOutputQuery, "MainRptWUser", acFormatXLS, , True

0
Utf
4/1/2010 4:10:06 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

2 Replies
1261 Views

Similar Articles

[PageSpeed] 20


"ThriftyFinanceGirl" wrote:

> I'm using the simple "OUTPUTTO" command to send a query over to Excel, 
> however we will have many users and we would like to programmatically add the 
> header rows, date printed, turn on the auto filter and freeze panes, all of 
> which I can do within a Macro in Excel, but how do I call that macro to run 
> from Access and How to run it on the file that they just created?
> 
> Current Code:
> 
> DoCmd.OutputTo acOutputQuery, "MainRptWUser", acFormatXLS, , True
> 
Here is the Macro Code in Excel:

Sub SetHeader()
'
' SetHeader Macro
' Macro recorded 4/1/2010 by Kari Jarrett
'

'
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Range("B1:D1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("B1:D1").Select
    ActiveCell.FormulaR1C1 = "JDE Upgrade Planner - REPORTS Listing by DEPT"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "Date Run:"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "4/1/2010"
    Range("C2").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B1:D1").Select
    With Selection.Font
        .Name = "Arial"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("A5").Select
    ActiveWindow.FreezePanes = True
    Range("A4:K4").Select
    Selection.AutoFilter
    Range("A5").Select
End Sub

0
Utf
4/1/2010 4:14:01 PM
Hi,

you can work with excel from other office applications using code similar to:

    Dim appExcel As Excel.Application
    Dim bksBooks As Excel.Workbooks
    Dim wkbBook As Excel.Workbook
    Dim wksSheet As Excel.Worksheet

  
    
    'Initiate all objects and variables
    Set appExcel = GetObject(, "Excel.Application")
    Set bksBooks = appExcel.Workbooks
    Set wkbBook = bksBooks.Open("give here the path + file name from the 
excel")
    Set wksSheet = wkbBook.Sheets(1)
    wksSheet.Activate

-- 
Kind regards
Noëlla


"ThriftyFinanceGirl" wrote:

> I'm using the simple "OUTPUTTO" command to send a query over to Excel, 
> however we will have many users and we would like to programmatically add the 
> header rows, date printed, turn on the auto filter and freeze panes, all of 
> which I can do within a Macro in Excel, but how do I call that macro to run 
> from Access and How to run it on the file that they just created?
> 
> Current Code:
> 
> DoCmd.OutputTo acOutputQuery, "MainRptWUser", acFormatXLS, , True
> 
0
Utf
4/1/2010 4:59:01 PM
Reply:

Similar Artilces:

Use Form to prompt for report criteria
I have a form that I am using to prompt for report criteria. When I run the query outside of the form, it works fine - prompting me for both criteria. However when I run from the form, I get #Error#. Can you see what I am doing wrong? Thanks in advance. I have two combo boxes that I have put in my underlying query. In the fields of the query are: [Forms]![frmSelection Criteria Form]![OfficeNumber] [Forms]![frmSelection Criteria Form]![Manager] *** On the OnClick event is the following: Private Sub Command6_Click() On Error GoTo Err_command6_Click Dim stDocName As String st...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

VLookup in VBA giving error message
Hi, I'm having trouble making the vlookup function work in VBA. the values that are used are all dates, I don't know if that makes a difference. Worksheets("sheet1").range("a1").value = application.WorksheetFunction. _ Vlookup(Range("c1"), range("a:a"), 2) this gives me a run-time error '1004' Unable to get the vlookup property of the Worksheet function class so i tried this: Worksheets("sheet1").range("a1").value = application.Vlookup(Range("c1"), _ range("a:a"), 2) but this gives me a #REF ...

is there anyway to access funcion written in c by vc++
hi Is this is possible to access ,a function written in C by VC++. Arbind >Is this is possible to access ,a function written in C by VC++. Yes. I suspect you need to know about using extern "C" to tell the compiler that your function is 'C' rather than C++. For example: extern "C" int MyFn( int p1 ); You often use it like this: extern "C" { #include "Your_C_Header.g" } Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq >#include "Your_C_Header.g" or even #include "Your_C_Header.h" who put that g key in the wa...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

Accessing MIcrosoft Outlook
Yesterday, when I tried to access Outlook in my home pC, I received the following message: Cannot open MIcrosoft Office Outlook. Cannot open the Outlook window. The set of folders cannot be opened. The file C:|Users \jillandgordon\AppData\Local\Microsoft\Outlook\Outlook.pst is not a personal folders file. Does anyone have any ideas as to how I can fix this? Masny thanks, Gordon in TX Try this first http://support.microsoft.com/kb/287497/ "Gordon McDougall" <braidwoodboy@gmail.com> wrote in message news:d0e17ac8-0215-431c-971f-bd862b58a245@y17g2000yqd....

I do not have access to the D: drive from Publisher, why?
I do not have access to the D: drive from Publisher, why? It keeps saying "incorrect function" khaganman <khaganman@discussions.microsoft.com> was very recently heard to utter: > I do not have access to the D: drive from Publisher, why? It keeps > saying "incorrect function" Is the D: drive your CD drive? -- Ed Bennett - MVP Microsoft Publisher ...

Accessing my information offline
I have updated my Outlook to 2002 on my notebook computer and when it is connected on the Lan at my office(new ethernet connection) I can see my calendar, addressbook, e- mails etc. However, when I disconnect and bring my notebook home, and try to access calendar, etc, it says I must sychronize while I am still connected to the Lan. My previous version of outlook didn't have this problem. I synched it on the Lan and it worked. This one won't. Any ideas???? How do you have your folders marked for Offline use under tools->Send/Receive settings? --� Milly Staples [MVP - Outlo...

Using atl based win dll with CString functions from the mfc projec #3
I have atl based general windows dll with class which contains functions which uses CString as parameters or return values. This dll might be used from the atl or mfc project. Dll can be used from ATL project without problems but whenever I try to use this class from the MFC project I get the following linker errors: error LNK2019: unresolved external symbol "__declspec(dllimport) public: int __thiscall MyClass::AddMenu(long,class ATL::CStringT<wchar_t,class StrTraitMFC_DLL<wchar_t,class ATL::ChTraitsCRT<wchar_t> > > const &,long)" ... If I replace CStri...

copying formulas in vba
Hey guys. I was wondering if someone could help me. I am writing a vba script that takes in data, analyzes it, and then copies the results to a new file. I am having a problem with two things. 1) I am using a template for the new file so there are a lot of formulas (sums and std) already defined and ready to use. However, there are some instances where there is a random amount of additional data I have to put in. So, I have to apply the same formulas to this new data. How do I copy formulas from one cell to another (allowing for a change in row) in vba? Lets say cell(1,4) has the form...

Not using zeros in graphing.
I have a running workbook that has tons of information. I have added a sum page in order to have all the data summed up in one simple place. I have formulas that read back into the workbook to link to a cell. Depending on what moth it is, that cell could be empty as it is a yearly wookbook. For example, if this is August, then there is information in the workbook up to August, but none after. With that said, the sum page has the #DIV/0! in the cell which essentially equals zero. I also have graphs that I have linked to this sum page. My problem is in order to keep the graphs up to...

Program Trying to Access Email Addresses #3
Outlook 2002. When I try to create new mail, forward, or reply I get a dialog box telling me: A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected, it may be a virus and you should choose "No". How do I determine what program is trying to access the Outlook addresses? I have up to date Norton Anti-virus definitions and have done a system scan, with nothing detected. Thanks for the help. ...

using vba so search multiple Sheets
i'm trying to search across multiple sheets based on data submitted via an input box. So essentially, you click the button, a box appears, you type what your looking for and if it finds it, it'll select it otherwise a error message appeats. i found the below code, but it only works if the cell with the value in it is active (i.e. i've clicked on it). Code: Dim datatoFind Dim sheetCount As Integer Dim counter As Integer Dim currentSheet As Integer On Error Resume Next currentSheet = ActiveSheet.Index datatoFind = InputBox("Please enter the value to search...

DPM and VMM on same server using side-by-side SQL installation
Windows 2008 Std R2 Server, 64bit VMM 2007 is currently installed (with the built-in default database) SQL 2005 Express Edition, SP3, 32bit. I was advised by Microsoft that both VMM and DPM could be installed on the same server (small DR environment) The VMs are hosted on a Windows 2008 Enterprise Server w/ VS2005R2 - because the hardware didn't support HyperV. VMM works great to manage the Windows 2008 VS2005R2 Host running Win2K3 server instances ... after lots of trial and error. Now, I need to install DPM 2007 onto the same server... DPM wants to install the 64bit vers...

Trying to open the current db in Access 2010 through Access Basic
I haven't programmed in Access Basic in quite a long time. Through Access Basic coding, I use to be able to open the current database and then open a table and walk through each record. Now I can't even open the database. Does anybody have an example of how to do the following: 1) Open Workspace 2) Open Current Database (assume name is unknown during run time 3) Open specific table 4) Step through each record and access the fields George C. Derkacs <George C. Derkacs@discussions.microsoft.com> wrote: >I haven't programmed in Access Basic in quite a lon...

MS Access 2007, Forms
When using the "Forms Wizard", I need to use the "Style: Ricepaper", but it isn't available in the 2007 version and I have not been successful trying to find it in Office Online or on the web. Does anyone know where I can get it? Access 2007 has 25 AutoFormats, but as you noticed, they are virtually all new ones. It's actually nice to see that these were updated. To get the older Ricepaper style you could probably create a form in an earlier version of Access and then import it into your current database. If you're using the ACCDB file format, you would...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...

Using "fixed" decimal place function data for charts
Is there a way to graph data which utilizes the "fixed" function for decimal places? When I try this it will not graph, as the data has become text or something - even though I've selected number in the cell formats. If you've fixed the number of digits using only number formats, the values remain numeric, and they should chart just fine. This means selecting the range, going to the Formatting menu and selecting Cells, and on the Number tab, selecting Number in the category list, and setting a number of digits. I'm not familiar with a "fixed function". I...

slow sync with Calendar using Kyocera 7135 via Outlook 2003
I'm stuck and need help. I have a Two Day Old Brand New Dell Dimension 4600. Does anyone out there know what to do when Outlook 2003 is giving a Log report that reads that Outlook Calendar - Slow Sync synchronization failed? This is a first time occurence for me. Outlook also reported that a device is trying to get access to my E- Mail addresses which may indicate a Virus may be present. I've run my McAfee Virus Protection and it didn't detrect anything. I'm at my wits end and don't know what else to do. Can anybody help? ...

invalid use of property message
I am trying to cre4ate a macro that checks the value of a number of sheets to determine if they are already visible and if so it will not close them - With Sheets("Pay Inflation - Biometrics") Sheets ("Statistics") Sheets ("Direct Cost Savings Breakdown") Sheets ("OT Reduction") Sheets ("Nurse OT Reduction") Sheets ("Premium Labor Utilization") Sheets ("Pay inflation - Timestamp") Sheets ("Calculation Error") Sheets ("Le...

Does table size make a difference in how well Access protects data
I am having a problem that has been going on for several weeks now. I am slowly trying to figure it out. the problem is that Access 2003 keeps deleting records after they are entered. Quick view of database: running Access 2k3 back end and frontends, 12 machines access the DB at any one time, mixture of Win2k Pro and Win XP pro all patches and service packs installed for all OS's as well as Access 2k3. Running Client/server set up controlled by Small Business Server 2k3. database resides on a machine running XP Pro. when new orders are entered into the system, the DB will tell the d...

can not Send/receive using Outlook 2003
Just bouhght new computer and am loading all of my software that worked onmy old one. When I try to send and recieve I get the message object not found. I have used test and this work ok. I have also reloaded all of my broadband and office software numerous times to no prevail. Software Windows xp Office 2003 ADSlL broadband with bigpond Gp Check to make sure the accounts are setup correctly if they are then run Detect and Repair, still an issue then create a new profile. May also want to check your anti-virus software program setting for outgoing mail and disable that option. Rega...

unable to link from Access to excel named range error 3011
I have a list of xl workbook names in an Access table. My code cycles through these names and finds them in a directory. This works flawlessly. Now I want to find and name ranges in the workbooks, and link to them from my Access db. The process works for the first 2, then fails on the 3rd. I don't see what's different about the 3rd tblName lclName CellTxt RngNm Active and Approved Pending Stores*.xls xlStores Location Code Stores Annualized Rent Batch*.xls xlExtras Store Number Extras ...

using rule creates multiple entries. How can I stop this?
I'm going to give you the details of this so maybe someone can just tell me how to correct or whether it can be corrected. I've tried creating different rules, rules with exceptions, multiple rules with multiple exceptions, all I can think. Here is the scenario. Although I'm using Lisa as an example, this problem applies to every staffer in my 9 person office. Lisa is my secretary. She sends me internal email, staff questions and stuff which goes to a a "Staff" subfolder of the Inbox. I have a rule for this. She also sends me mail she has scanned. I ha...

Giving Someone Access to Another Mailbox
I have a situation where one of our Admin Assistants is going to start sifting thru one of our executives email. What is the best way for this person to be able to see mailbox items? I would like to give this access via OWA only if possible and not neccessarily set it up on the client Outlook. Any help would be greatly appreciated. Which version of Exchange Server are you running? "mpallo" <mpallo@discussions.microsoft.com> wrote in message news:1CCFB8C2-E2EC-40B7-A5F2-27D37E856ABE@microsoft.com... >I have a situation where one of our Admin Assistants is going to st...