CODE for EXCEL output not executing in access - PrintTitleRows

Hello, 
MS Access/Excel 2003 on XP PRO.

I have a subroutine that outputs a query to an excel file and then formats 
it automatically, and everything works, I get no compile errors, except for 
one little failure.

when I execute the line with the > in front of it is the problem.


With obj_excel   'Excel application
'whole bunch of stuff in here

With .activesheet.PageSetup
>            .PrintTitleRows = "$1:$1"
           'plus other
end with

'whole bunch of stuff in here
end with

this line does not happen.  It executes, moves to the next line of code, but 
it does not happen.  the print title rows in the output excel file are not 
set.  the whole point of writing code like this is to prevent manually 
formatting a document so this irritates the hell out of me, since I may 
output 15 documents like this in a setting.

Any idea why this line of code is not executing?
Thanks.

-- 
BlueWolverine
MSE - Mech. Eng.
Go BLUE!
0
Utf
1/22/2010 12:11:01 PM
access 16762 articles. 3 followers. Follow

5 Replies
1158 Views

Similar Articles

[PageSpeed] 58

Ths code below is certainly more than you want but it does work.

Regards

Kevin

Sub FormatXLSheet(FileID As String)
    Dim xlApp As Object
    Dim wbExcel As Object
    Dim ws As Worksheet
    Dim lngRow As Long
    Set xlApp = CreateObject("Excel.Application")
    Set wbExcel = xlApp.Workbooks.Open(FileID)
    lngRow = 1
    On Error Resume Next
    Set ws = wbExcel.Sheets(1)
    With ws.Range("A1:AH1").Font
        .Bold = True
        .Name = "MS Sans Serif"
        .Size = 8.5
    End With
    ws.Range("A1:AH1").HorizontalAlignment = xlCenter
    ws.Range("A1:AH1").Interior.ColorIndex = 15
    With ws.Range("A1:AH1").Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With ws.Range("A1:AH1").Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With ws.Range("A1:AH1").Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With ws.Range("A1:AH1").Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With ws.Range("A1:AH1").Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

    ws.Range("A2").Select
    xlApp.Windows(1).FreezePanes = True

    ws.Columns("A:AH").Select
    ws.Columns("A:AH").EntireColumn.AutoFit
    ws.PageSetup.PrintTitleRows = "$1:$1"
    ws.PageSetup.Orientation = xlLandscape
    ws.PageSetup.CenterFooter = ""
    ws.PageSetup.LeftFooter = "Printed &D"
    ws.PageSetup.RightFooter = "Page &P of &N"
    xlApp.Workbooks(1).Close savechanges:=True
    xlApp.Workbooks.Close
    xlApp.Quit
    Set xlApp = Nothing
End Sub




"BlueWolverine" <BlueWolverine@discussions.microsoft.com> wrote in message 
news:81899CD7-626A-4851-A1D0-5BF4160C03A6@microsoft.com...
> With obj_worksheet.PageSetup
>            .PrintTitleRows = "$1:$1"
>            '.PrintTitleColumns = ""
>        End With
> With .activesheet.PageSetup
>            .PrintTitleRows = "$1:$1"
>
> end with
> I did this, with obj_worksheet=obj_Workbook.sheets("NAME OF SHEET")
>
> IT STILL DOES NOT WORK.  HELP!
>
>
> -- 
> BlueWolverine
> MSE - Mech. Eng.
> Go BLUE!
>
>
> "Douglas J. Steele" wrote:
>
>> I find that when Automating Excel, it's usually a good idea to avoid 
>> using
>> ..ActiveSheet, and instead explicitly refer to the sheet you mean.
>>
>> Take a look at my July, 2005 "Access Answers" column in Pinnacle
>> Publication's "Smart Access". You can download the column (and sample
>> database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html 
>> or
>> read it online at http://www.vb123.com/kb/200507_ds_excel.htm
>>
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>>
>> "BlueWolverine" <BlueWolverine@discussions.microsoft.com> wrote in 
>> message
>> news:DD6573F7-D81A-4989-95C3-24D179090D32@microsoft.com...
>> > Hello,
>> > MS Access/Excel 2003 on XP PRO.
>> >
>> > I have a subroutine that outputs a query to an excel file and then 
>> > formats
>> > it automatically, and everything works, I get no compile errors, except
>> > for
>> > one little failure.
>> >
>> > when I execute the line with the > in front of it is the problem.
>> >
>> >
>> > With obj_excel   'Excel application
>> > 'whole bunch of stuff in here
>> >
>> > With .activesheet.PageSetup
>> >>            .PrintTitleRows = "$1:$1"
>> >           'plus other
>> > end with
>> >
>> > 'whole bunch of stuff in here
>> > end with
>> >
>> > this line does not happen.  It executes, moves to the next line of 
>> > code,
>> > but
>> > it does not happen.  the print title rows in the output excel file are 
>> > not
>> > set.  the whole point of writing code like this is to prevent manually
>> > formatting a document so this irritates the hell out of me, since I may
>> > output 15 documents like this in a setting.
>> >
>> > Any idea why this line of code is not executing?
>> > Thanks.
>> >
>> > -- 
>> > BlueWolverine
>> > MSE - Mech. Eng.
>> > Go BLUE!
>>
>> .
>> 


0
kc
1/1/2010 4:31:05 PM
I find that when Automating Excel, it's usually a good idea to avoid using 
..ActiveSheet, and instead explicitly refer to the sheet you mean.

Take a look at my July, 2005 "Access Answers" column in Pinnacle 
Publication's "Smart Access". You can download the column (and sample 
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html or 
read it online at http://www.vb123.com/kb/200507_ds_excel.htm

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"BlueWolverine" <BlueWolverine@discussions.microsoft.com> wrote in message 
news:DD6573F7-D81A-4989-95C3-24D179090D32@microsoft.com...
> Hello,
> MS Access/Excel 2003 on XP PRO.
>
> I have a subroutine that outputs a query to an excel file and then formats
> it automatically, and everything works, I get no compile errors, except 
> for
> one little failure.
>
> when I execute the line with the > in front of it is the problem.
>
>
> With obj_excel   'Excel application
> 'whole bunch of stuff in here
>
> With .activesheet.PageSetup
>>            .PrintTitleRows = "$1:$1"
>           'plus other
> end with
>
> 'whole bunch of stuff in here
> end with
>
> this line does not happen.  It executes, moves to the next line of code, 
> but
> it does not happen.  the print title rows in the output excel file are not
> set.  the whole point of writing code like this is to prevent manually
> formatting a document so this irritates the hell out of me, since I may
> output 15 documents like this in a setting.
>
> Any idea why this line of code is not executing?
> Thanks.
>
> -- 
> BlueWolverine
> MSE - Mech. Eng.
> Go BLUE! 

0
Douglas
1/22/2010 12:59:43 PM
With obj_worksheet.PageSetup
            .PrintTitleRows = "$1:$1"
            '.PrintTitleColumns = ""
        End With
With .activesheet.PageSetup
            .PrintTitleRows = "$1:$1"

end with
I did this, with obj_worksheet=obj_Workbook.sheets("NAME OF SHEET")

IT STILL DOES NOT WORK.  HELP!


-- 
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


"Douglas J. Steele" wrote:

> I find that when Automating Excel, it's usually a good idea to avoid using 
> ..ActiveSheet, and instead explicitly refer to the sheet you mean.
> 
> Take a look at my July, 2005 "Access Answers" column in Pinnacle 
> Publication's "Smart Access". You can download the column (and sample 
> database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html or 
> read it online at http://www.vb123.com/kb/200507_ds_excel.htm
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
> 
> 
> 
> "BlueWolverine" <BlueWolverine@discussions.microsoft.com> wrote in message 
> news:DD6573F7-D81A-4989-95C3-24D179090D32@microsoft.com...
> > Hello,
> > MS Access/Excel 2003 on XP PRO.
> >
> > I have a subroutine that outputs a query to an excel file and then formats
> > it automatically, and everything works, I get no compile errors, except 
> > for
> > one little failure.
> >
> > when I execute the line with the > in front of it is the problem.
> >
> >
> > With obj_excel   'Excel application
> > 'whole bunch of stuff in here
> >
> > With .activesheet.PageSetup
> >>            .PrintTitleRows = "$1:$1"
> >           'plus other
> > end with
> >
> > 'whole bunch of stuff in here
> > end with
> >
> > this line does not happen.  It executes, moves to the next line of code, 
> > but
> > it does not happen.  the print title rows in the output excel file are not
> > set.  the whole point of writing code like this is to prevent manually
> > formatting a document so this irritates the hell out of me, since I may
> > output 15 documents like this in a setting.
> >
> > Any idea why this line of code is not executing?
> > Thanks.
> >
> > -- 
> > BlueWolverine
> > MSE - Mech. Eng.
> > Go BLUE! 
> 
> .
> 
0
Utf
1/22/2010 2:17:01 PM
On Jan 22, 7:11 am, BlueWolverine
<BlueWolver...@discussions.microsoft.com> wrote:
> Hello,
> MS Access/Excel 2003 on XP PRO.
>
> I have a subroutine that outputs a query to an excel file and then formats
> it automatically, and everything works, I get no compile errors, except for
> one little failure.
>
> when I execute the line with the > in front of it is the problem.
>
> With obj_excel   'Excel application
> 'whole bunch of stuff in here
>
> With .activesheet.PageSetup>            .PrintTitleRows = "$1:$1"
>
>            'plus other
> end with
>
> 'whole bunch of stuff in here
> end with
>
> this line does not happen.  It executes, moves to the next line of code, but
> it does not happen.  the print title rows in the output excel file are not
> set.  the whole point of writing code like this is to prevent manually
> formatting a document so this irritates the hell out of me, since I may
> output 15 documents like this in a setting.
>
> Any idea why this line of code is not executing?
> Thanks.
>
> --
> BlueWolverine
> MSE - Mech. Eng.
> Go BLUE!

Maybe I can help you follow Douglas' advice.  When formatting
worksheets from scratch, I reference each worksheet directly from the
Excel.Application object.  For example:

Set wsheet = obj_excel.Worksheets("Sheet1")
wsheet.Name = Format(Date, "mmm") & Format(Date, "yy")
With obj_excel
  .DisplayAlerts = False
  .Worksheets("Sheet2").Delete
  .Worksheets("Sheet3").Delete
  .DisplayAlerts = True
End With

James A. Fortune
MPAPoster@FortuneJames.com
0
James
1/24/2010 4:54:27 AM
"BlueWolverine" <BlueWolverine@discussions.microsoft.com> schreef in bericht 
news:DD6573F7-D81A-4989-95C3-24D179090D32@microsoft.com...
> Hello,
> MS Access/Excel 2003 on XP PRO.
>
> I have a subroutine that outputs a query to an excel file and then formats
> it automatically, and everything works, I get no compile errors, except 
> for
> one little failure.
>
> when I execute the line with the > in front of it is the problem.
>
>
> With obj_excel   'Excel application
> 'whole bunch of stuff in here
>
> With .activesheet.PageSetup
>>            .PrintTitleRows = "$1:$1"
>           'plus other
> end with
>
> 'whole bunch of stuff in here
> end with
>
> this line does not happen.  It executes, moves to the next line of code, 
> but
> it does not happen.  the print title rows in the output excel file are not
> set.  the whole point of writing code like this is to prevent manually
> formatting a document so this irritates the hell out of me, since I may
> output 15 documents like this in a setting.
>
> Any idea why this line of code is not executing?
> Thanks.
>
> -- 
> BlueWolverine
> MSE - Mech. Eng.
> Go BLUE! 

0
sduis
1/24/2010 6:03:04 PM
Reply:

Similar Artilces:

access 2007 crashes
when i change by code the controlsource of a textbox while opening the report, access crashes. is there a way to solve this (besides opening first in lay-out view)? michel Yes, this is one of the bugs in A2007. AFAIK, no resolution has been provided for this problem yet. Hopefully we will see it fixed when MS releases SP1 for Office 2007. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Michel Peeters" <michel@melsbroek.be> wrote in message news...

Accessing Record Buffer in MCI
Hi all, I am using MCI to record user audio through mic or any other recording device. While I can play it back and save the audio easily, I have not found any method to directly access recorded buffer. How do i do this? Thanks in advance nightcrawler wrote: > Hi all, > > I am using MCI to record user audio through mic or any other recording > device. While I can play it back and save the audio easily, I have not > found any method to directly access recorded buffer. > > How do i do this? > > Thanks in advance I think you can save your recorded buffer to a mem...

Deleting Phantom Lines in Excel
I am finding phantom lines when I print. They are not in the file and don't show up on the print preview. Please help ...

Excel Startup Error
Whenever I double click on my Excel icon I get a message that `C:\Program files\Microsoft office\office 10\library\mobiledb.xla can not be found. I tap on the OK button and immediately go into Excel but this error notice is getting on my last nerve! I've searched and sure enough it's not on my computer. I was looking for an Excel.ini file so that I could remove Excel's search for this bogus file but I can't figure it out. Any help would be wonderful as I'm tired of looking at this useless error. Thanks in advance, Mick I think it is the Palm database add-in. Have yo...

Cannot logout of Outlook Web Access in 2003
We are having a problem here at work with Outlook 2003 Web access. We have multiple users using certain machines that are logged on under the one global account. When we were running Outlook Web Access under 2000, it would log out no problems, but now with the upgrade to 2003, it keeps the same settings from the one user, even if we click the log out option. Is there a setting i have missed somewhere, or a way aroud this problem? We can log in as others, if we type in that particular users username at the end of the html address. But this really isnt practicle as some of of users a...

.snp Sent via Access-to-Outlook Automatic
W/ a filename of Sales030408_030908.snp - is NOT opening. What am I doing wrong? TIA - Bob Hi Bob, My guess is that you are not doing anything wrong. It may be that some setting is not correct in your registry to allow Outlook and Snapview.exe to work together correctly. Are you able to open the .snp file if you first save it from the e-mail message to some folder on your hard drive? Have you tried a different .snp file, just to rule out possible corruption in the Sales030408_030908.snp file? Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.q...

Updating "Master" Excel Worksheet
I'm in a national law enforcement association with over 10k members. We keep our national membership records in Access. Replication would be wonderful tool, but not a lot of the chapters have Access, but most of the members have Excel. We would like to send each chapter their membership info in an Excel worksheet for them to update and then return to us to update our national records without changing each entry by hand. I'm not sure if this is an Access or Excel question, or maybe both, but is there a way to do this using the automated features? I'll be eternally grateful...

opening excel from intranet ie6.0
I have a number of excel files I wish to share between multiple users. I can give them access to these files via our Intranet using Internet Explorer 6.0. My problem is that the excel files open up inside the browser, not in excel. I want user to click on the file in the Intranet IE6.0 and for the file to open in the Excel application (Excel 2000). Any ideas / help welcome. Thanks Paddy ...

Accessing lookup entity properties!!
Hi, I have found a useful piece of code which allows me to use Javascript to read certain properties from a lookup entity. In other words, when I create a referential relationship between my custom entity and the product entity, I would like to have the Javascript code write certain product characteristics, such as ID, Description, etc. to other attributes on the form. Is this possible or can I only access the name value? This is a piece of code which will retrieve the name value of the lookup, but I would like to access more product attributes. var selectedProduct = crmForm.all.<produc...

Excel messes my data up
Hi, I am trying to run vlookup for about 5K cells from range of 20k cells. Now the problem is that some of the cells include only numbers and some both numbers and letters, like AB11002. I also have empty spaces on the data so I have to run trim on both data before running vlookup. Just after trimming both data vlookup runs correctly, but if I "touch" any cell containing only numbers excel propably converts it to number and at this point excel don't think those two cells that before was equal are equal any more. So how in the earth I could convert my mixed data in the format e...

automate 2dline with marker chart generation
In generating line charts with markers, I can change the color of the markers, but not the marker lines and series lines. The VBA code for the markers is: .SeriesCollection(1).Select With Selection .MarkerStyle = xlDiamond .MarkerSize = 9 End With .SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 200, 25) What are the references for the marker lines and the lines between the series points? Excel 2007 Chart Format markers and point line segments. http://c0444202.cdn.cloudfiles.rackspacecloud.com/10_31_09a.xlsm the "Herbert Sei...

Phone Numbers for Access MVPS
Id' like to start posting phone numbers for Access MVPs; that way if you need help with Access then you can go ahead and call them I found his address and phone number at zabbasearch.com TOM G WICKERATH 15851 12TH PL BELLEVUE, WA 98008 (425) 562-8913 I will post more soon oh and here is the contact info for Tony Toews, right?? http://www.zabasearch.com/query1_zaba.php?sname=anthony%20toews&state=ALL&ref=%24ref&se=%24se&doby=&city=&name_style=1 I love this website; this is so helpful-- now we can CALL our favorite MVP whenever we have a problem with Acce...

Excel book #2
Witch is the best excel book? marquetta.kayes@booomail.com Depends upon where you start from, and what you want to achieve. Check out John Walkenbach's Excel 2003 Bible as a start. -- HTH RP (remove nothere from the email address if mailing direct) "marquetta kayes" <marquetta.kayes@livepharm.com> wrote in message news:1a5c7$42e4a746$d99c5301$8690@allthenewsgroups.com... > Witch is the best excel book? > > marquetta.kayes@booomail.com Considering you can get it delivered to your inbox, free, you might want to try MrExcel's new book. http://www...

Data Access Page 09-11-07
Good Morning Community, Is there any way to include a search function on a Data access Page? I have a file that I want to put on a Data access page that will be published on a shared drive, the problem is that the file has about 1000 records and to search through each one would defeat the purpose of putting the page up. Is there anyway to include a search function that would jump to a particular record as access does on its forms. Thanks in advance for any response Perhaps this sample database will serve as a starting point: http://www.cadellsoftware.org/SampleDBs.htm#FilterDAP...

Error Code 646 02-28-10
While trying to perfom the following windows updates I get an error code 646. I am using windows vista. Can somebody please help? 2007 Microsoft Office Suite Service Pack 1 (SP1) Security Update for Microsoft Office 2007 (KB951550) Security Update for Microsoft Office Excel 2007 (KB959997) Security Update for Microsoft Office OneNote 2007 (KB950130) Security Update for Microsoft Office PowerPoint 2007 (KB951338) Security Update for Microsoft Office Publisher 2007 (KB950114) Security Update for Microsoft Office Word 2007 (KB956358) Security Update for the 2007 Microsoft Office Sy...

Excel into Word
I have an Excel document that has a column for each section of an address (Name, Street, City, ect.) Is there any way for this to be put into block format in Word to print out an address list? Thanks! May work with a mail merge in word - that will read a excel spreadsheet "Jeni" <anonymous@discussions.microsoft.com> wrote in message news:13ce001c3f7e3$cdfde030$a001280a@phx.gbl... > I have an Excel document that has a column for each > section of an address (Name, Street, City, ect.) Is there > any way for this to be put into block format in Word to > print out a...

Excel Formatting #3
I want to know how to format rows into even columns. For example, my spreadsheet has 160 rows that I would like to format into 3 even columns across a landscape page. Can anyone tell me how this is done? ...

mde file in Access 2007
I have just converted a mdb database in Access 2007 to a mde file, but now the said database does not work correctly. When I opened up the mde database, I got a message from Access 2007 saying there was a potential security concern, and I clicked the ok button to open up the database. Now the combo box to find records does not function, as when I select a record from the list, it is not shown on the form. Thanks On Sun, 30 May 2010 18:00:40 +0100, Bob H <bob@despammer.com> wrote: Did you put the database in a Trusted Location (see help file)? -Tom. Microsoft Access M...

CArray of stdext::hash_map<int,int> not possible ( access violation ) ?
Hello, I have problems using CArray<> with hash_map<>. What's wrong with the following code ? class A { A() {} A( const A& ) {} ~A() {} void operator=( const A& ) {} stdext::hash_map<int,int> m_member; }; .... A item; CArray<A> test; test.Add( item ); test.Add( item ); test.RemoveAll(); // access violation in hash_map<> destructor ???????? The same code with std::map<> works ok. What is the problem ?. Everything tested under VS.2005. Thanks, Adam "Adam" <adam_mich@gazeta.pl> ha scrit...

code error 80048823
hi i have tried every thing to fix it reset my password but i wont work and i dont have a alternate email adress option can you please help mee and i tried looking at all solution i stiil aint workink helpp please ... ...

How to delete/update controls and related codes?
Hello all, I am using VS 2005 to generate a MFC application. I am wondering after I generating an application using Unicode, how can I delete it without regenerating the application? Also, if I use recourse editor to add some controls and class wizard to add variables to connect these controls, how can I delete or update them completely? If I delete a button from the resource editor, I have to delete some related codes elsewhere. I also found that class wizard will change variables into grey (do not allow to change). What should I do? Thanks in advance. Zhidian Du You just have to...

Macros in Excel #5
How can i create a macro that changes what is in a drop down box by what is selected in another dropdown box? You could record a macro of your changing the validation list. However, you can do it without a macro using the technique here: http://contextures.com/xlDataVal02.html In article <558CC81E-4F2C-4A4C-B9A6-0B8E533722D2@microsoft.com>, "alhawtin" <alhawtin@discussions.microsoft.com> wrote: > How can i create a macro that changes what is in a drop down box by what is > selected in another dropdown box? ...

Unable to open excel file #2
Hi. I have problem openning an excel file from windows explorer or from my desktop. However, when I open the excel program and open a file manually(File-Open----) everything works fine. I get an error message that the file could not be located. I noticed that it adds .xls to every word separated by space. For example: My file is in C:/Shared/Folder/test.xls, it tries to open: C:/Shared.xls then Folder.xls then test.xls Please help. Thanks. --- Message posted from http://www.ExcelForum.com/ Hi! I think the steps below will solve this problem: 1. Exit Excel 2. From the Startmenu, cli...

Excell
How do I change font colour in a footer?? There are options to change the font, size, underline etc but not colour... Can anyone help? You can't change the font colour in a footer. If you really need coloured text, you could print just the footer using Word, then print the Excel file onto those pages. Or, if you have Excel 2002, you can insert a coloured picture into the footer, so you could create a graphic with the text, and insert that. cinabar wrote: > How do I change font colour in a footer?? > > There are options to change the font, size, underline etc > but not...

EXCEL #152
excel BIBBYBIBBER - Nov 18, 9:41 am When I enter a number in a newly opened fresh spreadsheet the number is automatically divided by 10.0. When I enter a number below 1.0 this effect does not occur. I have used Excel for over 8 years and never have experienced this phenomenon, it is driving me crazy. Can someone please tell me what is going on and how to fix it? Tools>Options>Edit, uncheck Fixed decimal -- Kind regards, Niek Otten "BIBBYBIBBER" <ALANFREDERICKA@AOL.COM> wrote in message news:1132328918.399314.137500@g49g2000cwa.googlegroups.com... > excel > B...