i hope this works Results

Page 49 of 50.
Results 961...980 of 2,428

Sponsored Links:


I hope someone can help me...I have a user who has been working on a file for sometime now. The file is 34.5MB!, now when she or I try to open it, it get's half way then stops...the processor time goes up to 100% and the hour glass appears. Task Manager says 'not responding'.

I am presuming the file has just got too large now and was wondering if anyone knows a way around this such as splitting it in 2?, or if it has been encountered before?

If anyone has any ideas I'd be very grateful...



Hi All!

I'm hoping someone can help me... I've been working on creating a different way of allocating interest inventory for my company... It took a long time but I've finally got the old way to tie in to the new way...
Yahoo!!! I'm balanced!!!

What I need from you is to know if... I have added 2 or more levels of Subtotals on a sheet... By Currency, then Dept, etc... and another sheet with different levels of Subtotals... Can I, in code, create a new table on a third sheet that uses the different subtotal levels to compare/balance...

What I mean is... I hit the button I created and code runs that drags in the records I want and then adds the levels of subtotals...
Now I'd like to go to a third sheet and list the currencies, depts, etc, and bring in subtotal data from the other sheets to compare and show balanced...

In code can I say something like...
Find Sheet1's Canadian Currency section, Dept 001's Total and put it here... Next to it put Sheet2's Canadian Currency section Dept 001's Total... etc...

I'm sorry... I don't think I'm making sense....

Okay... Let's try this... The code below makes the subtotals after the new data is updated... (It works, but please feel free to edit this if I'm doing something silly...)

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, 9, 10), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, 9, 10), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True

Can I now use something like Sheet3, Cell(whatever) = Selection.Subtotal Group1?... or do I have to search for the lines with the words "Dept 001 Total", etc and offset to the right column?

in Advance for any ideas!!!

Hello there!

Here's my problem: We have several Users (> 25) who are using Excel 2010 to get the work done. During the day it is inevitable that someone tries to open a file someone else is currently working on. Resulting in a message "File locked ... by 'another user'...". In Word 2010 the message says exactly which user is blocking the file. But Excel 2010 insists on 'another user', regardless the fact that the username is existing under options.
Since there are so many users, who are placed all around the building, it is quite difficult for them to find the one. So finally the end up with me, and I locate the one via the server. Although it can be done this way, I would prefer if Excel could just use the existing username. I tried to google my way to find a solution, but didn't find a decent one.
So I hope the combined knowledge of the lounge can assist lil' ol' me.

Hi All,

I have a timesheet spreadsheet which is used by staff to calculate total hours worked per week.
My problem is, I need to enter a negative time value for staff who are carrying over hours from the previous week so it is duducted from the current week to calculate total hours that need to be worked.
When I enter -3:25:00 in a cell I get a excel warning message informing me that the formula I have entered contains an error.

Sorry for the long winded post, I bet there is a very simple answer to this! (I hope)




I have a question I hope someone can answer. I fequently work with a number of large documents in Excel (97). I often do a quick look at them in printview to ensure that they will look decent when I print them. My current document has 54 pages. Is there a way to use print view to just look at the last several pages eg pg 48 to pg 54? It gets a bit tedious continually clicking the NEXT button to get to page 48.

I've always figured when you are doing something that is repetitive in Excel, there is usually a faster/better way to do it, it's just a matter of figuring out how.

Thanks to anyone who might know of a way to do this.

StatusMsg = _
"=CONCATENATE(TEXT(RC[1],""mm/dd""),"": Vendor Confirmed PO. "",R[-7]C)"

This Variable (StatusMsg) takes the date stored in the adjacent cell on the same row (hence RC[1]), changes it to text, then, concatenates it with the text "Vendor Confirmed PO", finally (and here is the problem), it also concatenates it with the contents of the cell stored 7 rows above it.

This works if I assume I am always on Row 10. I know longer want to assume I am on Row 10. The value, though, is always stored in Row 3 of that column. What is the syntax I should use? Hope that made sense!

Can anyone see any inherent error in this formula? The formula resides in the cells in column in "L". It worked fine as shown in the earlier version (below), but after making changes today as shown here I got some weird results.

Weird result version:
=IF(OR(C16="",K16=""),"",IF(OR(J16="NO CHARGE",J16="N/C"),"",IF(AND(J16=0,K16".00"),C16*K16,IF(K16". 00", C16*(ROUND(J16,0)+K16),C16*(ROUND(J16,0))))))

Previous to today's changes I ran the formula this way with no problem:
=IF(OR(J16="NO CHARGE",J16="N/C"),0,IF(C16*J16=0," ", IF(K16".00", C16*(ROUND(J16,0)+K16),C16*(ROUND(J16,0)))))

Right after creating the changes and copying the revised formula to all the cells in column "L" I got some blatant math errors. If I put a value in J16 first, and then a value in C16 (C16*J16), I would get the right answer. However, if entered a number in C16 first.....which is how it would be done in daily use...... I would get the math error. I don't mean an "error message".....I mean a "math error" such as 2*2=5. Doesn't seem possible but it was happening.

This happened for 3 or 4 minutes of experimenting to find out what was wrong and then the problem just went away on its own......I didn't change anything in the formula. Since this formula repeats in every cell in column "L" from L15 to L38 I would have had to copy any unintended change all the way down the column....which I didn't do. Very weird. Now even though it is working right I don't trust the formula.

My only hope is I've done something stupid that Excel might be reading different ways on different occasions for some reason ???

It might help to know that:

C16 is "quantity of items"

J16 is cost in dollars (entered as dollars and cents but rounds up or down)

K16 is cost in cents (this column is formatted as text. The default ".00" displays if there is a dollar value in J16. It is sometimes overwritten manually with a cents amount such as ".50". Even though it is formatted as text it multiplies out properly into column "L".)


Hopefully this is an easy question that's just not obvious to me on 3 hrs of sleep.

I need to change the font color to red for all text included in bullet or number lists. I found the code below in the Word help file which works fine if I wanted to change the background but I don't know how to get it to change the foreground. How can I do this?

For Each numpar In ActiveDocument.ListParagraphs
numpar.Shading.BackgroundPatternColorIndex = wdYellow
Next numpar


Hello, I'm writing some code that reads some user attributes out of Active Directory and populates a user form with the attribute values. Previously I have always done this by populating the user form with values from the Registry, so this is new to me. I have the code working to read all the attributes and just a particular attribute, and it runs just fine as long as it is not called from within the user form code. For example, the following sub works:

Public Sub test()
'gets the login ID of the logged in user, passes it to a public variable - works fine
'Parameters are the public var for the login ID, and the AD attr name; it returns public var sAttr which contains the value of the AD attr
basUserADInfo.GetUserAttr LoginUserName, "firstname"
MsgBox sAttr 'displays the value just fine
End Sub

The code which gets the attribute - basUserADInfo.GetUserAttr - follows below:

Public Function GetUserAttr(LoginName As String, sAttrib As String) As String
'PURPOSE: Display information that is available in the Active Directory about a given user

Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim oRoot As IADs
Dim oDomain As IADs
Dim sBase As String
Dim sFilter As String
Dim sDomain As String
Dim sAttribs As String
Dim sDepth As String
Dim sQuery As String
Dim user As IADsUser

On Error GoTo ErrHandler:

'Get user Using LDAP/ADO
Set oRoot = GetObject("LDAP://rootDSE")
'Work in the default domain
sDomain = oRoot.Get("defaultNamingContext")
Set oDomain = GetObject("LDAP://" & sDomain)
sBase = ""
'Only get user name requested
sFilter = "(&(objectCategory=person)(objectClass=user)(SAMac countName=" & LoginName & "))"
sAttribs = "ADsPath"
sDepth = "SubTree"
sQuery = sBase & ";" & sFilter & ";" & sAttribs & ";" & sDepth
conn.Open "Data Source=Active Directory Provider;Provider=ADsDSOObject"
Set rs = conn.Execute(sQuery)

If Not rs.EOF Then
Set user = GetObject(rs("ADsPath"))
With user
sAttr = user.Get(sAttrib)
End With
End If


On Error Resume Next
If Not rs Is Nothing Then
If rs.State 0 Then rs.Close
Set rs = Nothing
End If

If Not conn Is Nothing Then
If conn.State 0 Then conn.Close
Set conn = Nothing
End If

Set oRoot = Nothing
Set oDomain = Nothing

End Function

Now, the code I am using to populate the user form is:

Private Sub UserForm_Initialize()
Dim ctlText As Control
basUserADInfo.GetUserAttr LoginUserName, "firstname"
txtFirstName.Value = sAttr
End Sub

I actually had this setup so the basUserADInfo.GetUserAttr procedure was a function that returned the attribute value, so I could run the following code, which cycles through all my tagged controls (which are named the same as the AD attributes) and populates them:

For Each ctlText In Me.Controls
If ctlText.Tag "" Then
ctlText.Value = basUserADInfo.GetUserAttr(LoginUserName, ctlText.Tag)
End If
Set ctlText = Nothing

But, when that didn't work I started simplifying the code (to what I have at the top of this post) in an effort to figure out what wasn't working. Finally, I got the code so darn simplified, as you can see, and it works like a charm unless I try to call it from the user form...so I am at a loss.

I hope someone out there can point out what I am doing wrong. I just hate ending the day stuck!

Thanks to all you gurus,

The programmers at my company are plagued with issues related to the Package and Deployment Wizard. When writing an application and then creating an installation package, then trying to run this package on the server, EVERY SINGLE TIME it will announce it is copying files, then present you with an OK/Cancel window stating that the system needs to reboot (assumably to register the controls just copied). So you reboot the system and run the install again, it copies the same files, and again, presents you with the same window stating you have to reboot. This never ends. What we have been doing is using Regsvr32.exe to manually register the controls from a command line. For files that are in use we would reboot into a DOS mode, explicitly delete the file and copy in the new file (such as scrun.dll). We found one time that these copied files were stored in an obscure directory (system32msftpdw.something) so we wrote a program to loop all of the files and attempt to execute the regsvr32.exe on each. This worked for all files with the exception of the shared, of course.

Can't begin to tell you how time consuming and annoying this is - especially if you are deploying to several fat clients. I even called Microsoft Support who didn't identify it as any bug so they woud charge us for just searching the knowledge base and sending us the article. So, we declined their service and searched the KB ourselves (again) and didn't come up with anything.

Someone else out there must have run into this issue, and hopefully, has found a better way around this.


Sorry I have been away for a while, and now I can come up for some air and send a request not to shoot me first then read this second, but to the opposite.

OK my Boss is so proud of what he has done some 20 years ago, that there is no way I am going to convince him to let me upgrade the systems he has put in place.

In a routine task of looking at internal processes, we discovered that my Boss has written some QuickBASIC code that handles some sensitive issues that is still working on MS-DOS and Dot matrix printers. To be honest with you, I am quite impressed by what he did, some 20 years ago...

BUT!!! he is not going to let me touch it and move it into a VB or VBA version and I tried. There is a problem, and we need to fix the program so that it can do a form feed on a dot matrix printer.

Now I know that this is a huge deal, and maybe manually one will still get it wrong, but I was hoping to even allow the printer to reset say if someone counts the sprockets and then the code will rewind or forward by what ever it needs to print.

I printed a report, and it came out on half the page, because I did not know I had to reset the page to a certain position. This is where the problem came up, and my Boss is adamant on keeping his legacy alive...

So even if the answer is "can't do it" please let me know, maybe the preponderance of evidence will help him wise up.



I'm currently working as a temporary full-time employee for a company who has been a client of mine for almost 15 years. I'm hoping to have it turn into a full-time permanent position, but, right now, they don't have a position in the company for anyone to do what I do, basically custom application development in Office, automated templates and forms, training, integration with other applications and databases, Access database development, etc. I'm a Microsoft Office Specialist - Master Instructor as well as a VBA developer, and I think I have a lot to offer this company. However, I have to convince them of that. So I'm trying to put together documentation that will help sell the value of having someone around who can do this kind of work, one piece of which would be a potential job description. So, I'm looking for input from any of you who are doing this type of work full-time, or even part-time if it is an actual position and not just something you do in addition to your regular job because it's so much fun. If you have a job description, could you either post it (if you think others might find it of value) or send it to me directly? Thanks!

If you've been through something similar and have suggestions on how to approach the powers that be, I'd appreciate that input, too. And anything else you think might be helpful. This company just topped 500 employees, with offices all over, and their one VB programmer doesn't really deal with Office at all. Oh, and if you have any input on appropriate salary ranges, I'd appreciate having that information, too. This temporary position started March 1, for just this month, and so I need to get something put together quickly.

Many thanks!


Hi! I hope someone can help or point me in the right direction here. I am working on a vb app and I need to import a text file into the program(database) and I don't have a clue as to how to do this. My app has a database named layouts that contains 4 fields one of which is a part number field. I need to match that part number field up to the part number field in a text file (the text file is generated by a proprietary system and there's no other way of getting the info out.) and only display those matching records. (If theres a part number in the layouts database, I need only the matching records from the text file.)
The text file is a +-sign delimited file and the user needs to be able to press an Update button and have this file imported into the program and displayed in a table-like fashion or grid. The info in the text file is read only and does not need to be edited.
I am a vb newbie and really have no idea where to even begin on this. ALL help is VERY much appreciated! Thank You!

I'm looking to color half a cell (the left or the right side). I know there are tricks in Excel itself but as far as I've seen, nothing comes close to normal shading (and yes, I think I know this is easier with Excel 2007).

So, I'm thinking of doing with with VBA. No very complex I think... make a graphical element and align it to the cell left or right. Make the graphical element the same height and half as wide as the underlying cell, give it a color, remove the borders and bingo (I think and hope...).

Of course I can try and build this but quite frankly, I'm hoping that some code is already available that does this (or comes close)... someone must have done this before!

I'd appreciate any suggestions and will post back my working code here after I'm done.

Oh, by the way, I'd need to have a way to select a cell, and via worksheet.change event find if a graphical element is already there and either update or remove that... To be honest, if I think about it, that seems the more difficult part of the code: how do I identify a graphical element that is on top of the current cell?

Hi to all -- hope this makes some sense ....

I'm working on a project where a user will be sending an email with a special Subject line and possible text in the body of the email. The email will be read by an Outlook client, who (perhaps thru Outlook Rules) will cause any number of different VBScript programs to be executed. OK, no trouble so far ..... The trouble begins with the actual VBScript program. The code in certain scripts are accessing some areas of the network where higher network rights are needed. Since the VBScript is inheriting the rights of the Outlook client when running (this account has limited rights), the VBScript will error out on a rights access issue.

So, my question is: Can someone come up with a way in code that I can change the authentication access such that the VBScript program will complete OK? I do not want to give the Outlook client any kind of Supervisor access. I keep thinking there must be some way to do this ...... Any help or guidance is appreciated!

Oops -- environment is Win2K and Office2K.


Andrew Harrell
vb Padawan Learner

At times it is a nuisance that built-in styles (like Normal or heading 1) can't be renamed in Word.

Say you want to copy parts of one document into another with different style definitions, but want to keep the formatting. This is nearly impossible if both documents use built-in styles, because you can not rename the styles in one of the documents. So a paragraph formatted in Normal style will acquire the formatting of Normal in the document you paste into, and there is not much you can do about it (short of using "paste special" as a Word document object).

Another case where I found this a nuisance is when I take Word documents into a DTP program. Say the chapter headings in the DTP program are supposed to have style H1. Then I have to rename heading 1 in the DTP program, which often has to be done manually.
A macro solution in Word would be preferable.

The following macro renames all styles in the document by appending a * to the style name (heading 1 is changed to heading 1* ...).
Since heading 1* isn't recognized as a built-in style anymore, I can then rename it to anything I want (for example to H1).

The macro works by saving the file in RTF format, and changing all style names in the header of the RTF file.

Hope somebody else will find this useful, too.
The usual disclaimer: Use the macro at your own risk


Sub ChangeStyleNames()
' The macro appends a * to all style names
' It thus changes built-in styles to ordinary styles
Dim myRange As Range
Dim MsgText
Dim myFileName

MsgText = "Cancel if you have not saved the file"
If MsgBox(MsgText, vbExclamation + vbOKCancel, "Danger") = vbCancel Then
End If

myFileName = ActiveDocument.Name
If InStr(1, myFileName, ".") > 0 Then
myFileName = Left$(myFileName, InStr(1, myFileName, ".")) & "RTF"
myFileName = myFileName & ".RTF"
End If
ActiveDocument.SaveAs _
FileName:=myFileName, _
Documents.Open _
FileName:=myFileName, _
ConfirmConversions:=False, _
Set myRange = ActiveDocument.Content
myRange.Find.Execute _
FindText:="{stylesheet*}}", _
myRange.Find.Execute _
FindText:=";}", _
ReplaceWith:="*^&", _
MatchWildcards:=True, _
Documents.Open _
End Sub

Edited by dansy on 16-Aug-01 17:04.Hi,
hope someone can help with this as its driving me nuts.
I have a set of vba subs (see extract below) that open a directory and scans each file (opened read only) of a given mask for text of a given mask. Results are put into a new document for each source file with the .text element of the target find being pastespecial'd as a hyperlink in column 1 of the target document table row.

This works fine sometimes on the 3 test source files, sometimes processes 2 then bombs (error 5342) and sometimes only does the first file. The first file is always processed successfully and the target file it bombs on is successfully opened and the text found, the newdoc created and the table inserted, but fails on the first pastespecial with error 5342. This is because the ability to paste as a link is not available (in a newly created document!!) but I have no idea why. I have searched and searched for hits on 5342 to no avail.
Note that even when I stop the macro and go into the newdoc the Paste as Link option is not available. The target document is not protected.
Any help on this would be appreciated as I am tearing my hair out - and there aint much left to tear out!

Sub req_extract_main
Set fs = Application.FileSearch
With fs
.LookIn = "D:vba code tester"
.FileName = "abcd-*.doc"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
total_file_counter = .FoundFiles.Count
MsgBox "There were " & total_file_counter & _
" REK*.DOC file(s) found."
For i = 1 To .FoundFiles.Count
Documents.Open FileName:=.FoundFiles(i), ReadOnly:=True
Selection.HomeKey Unit:=wdStory ' Set pointer to start of doc
search_source = .FoundFiles(i) ' Set the source document to the first window
Set sourceDoc = Documents(.FoundFiles(i))
OpenNewDoc ' Create the target doc
ScanReq ' Scan the target and populate the New doc
Documents(.FoundFiles(i)).Close SaveChanges:=wdDoNotSaveChanges

Next i
MsgBox "There were no suitable files found."
End If
MsgBox "A total of " & total_file_counter & " files were searched of which, " & yes_file_counter & " had matching content."
End With
End Sub


Sub ScanReq()
With sourceDoc.ActiveWindow.Selection.Find
.Text = "[^#^#^#^#:^$^?:^#^#^#]" ' Set the search mask and find criteria
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
sourceDoc.ActiveWindow.Selection.Find.Execute ' Perform the find then assign vars to the
' results
If sourceDoc.ActiveWindow.Selection.Find.Found Then
With sourceDoc.ActiveWindow.Selection
found_text = .Text
found_sentence = .Sentences(1)
found_page = .Information(wdActiveEndPageNumber)
End With

With newDoc.ActiveWindow.Selection ' Check insertion point is in the table then
If .Information(wdWithInTable) = True Then ' add a row and paste the results
End If
found_counter = found_counter + 1
sourceDoc.ActiveWindow.Selection.Copy ' copy the .text portion so we can paste the link
Documents(newDoc).ActiveWindow.Selection.PasteSpec ial Link:=True, DataType:=wdPasteHyperlink
.MoveRight Unit:=wdCell, Count:=1
.Text = found_sentence
.MoveRight Unit:=wdCell, Count:=1
.Text = found_page
.MoveLeft Unit:=wdCell, Count:=2
End With
End If
Loop Until Selection.Find.Found = False
If found_counter > 0 Then ' We had at least one match so sort them
sort_results ' Sort the results
yes_file_counter = file_counter + 1
With newDoc
.SaveAs FileName:="Results of scan of " & Left(sourceDoc.Name, 9)
End With
Documents(newDoc).Close SaveChanges:=wdDoNotSaveChanges
End If
End Sub

It bombs on the first paste on the second file EVERY time now.

Hi, I'm new here, so I hope I've posted this in the right place!

I've converted a PFD file to Word. The file contains hundreds of hyperlinks that point to places in the same file. My problem is that these hyperlinks all point to the header of the target page, rather than the target itself - a result of the conversion.

So I firstly wrote a macro that finds all the headings and subheadings and converts them into bookmarks. This seems to work quite well, but not all the headings are picked up, even though they all have the same font. I then wrote a macro that took all the hyperlinks, compared them to the bookmarks, and if there was a match, stored the bookmark as the new subaddress of the hyperlink.

However, I don't seem to be able to get this second part working. I've never studied VBA before, so its all getting rather confusing for me! I was wondering if anyone could look at my code and tell me where I'm going wrong?

Sub GetBookmarks()

Dim SenRange As Range
Dim j As Integer
Dim Addy As String

On Error Resume Next
For Each SenRange In ActiveDocument.Sentences
If SenRange.Font.Name = "Helvetica" Then
SenRange.HighlightColorIndex = wdYellow
Addy = SenRange.text
Addy = Replace(Addy, " ", "_")
'MsgBox Addy
ActiveDocument.Bookmarks.Add Name:=Addy, Range:=SenRange

End If

End Sub

Sub ConvertHLinks()

Dim i As Integer
Dim HLinkName As String
Dim oBookmark As Bookmark
Dim oBookmarkName As String

For i = 1 To ActiveDocument.Hyperlinks.Count
HLinkName = ActiveDocument.Hyperlinks(i).TextToDisplay
HLinkName = Replace(HLinkName, " ", "_")
numWords = UBound(Split(HLinkName, " ")) + 1
HLinkName = Left(HLinkName, InStr(HLinkName, "(") - 2)

For Each oBookmark In ActiveDocument.Bookmarks
oBookmarkName = oBookmark.Name
If InStr(1, oBookmarkName, HLinkName, vbTextCompare) = 1 Then
MsgBox oBookmarkName
ActiveDocument.Hyperlinks(i).SubAddress = oBookmarkName

End If
Next oBookmark

End Sub

Many thanks, Oliver

This is my first post in this corner of the lounge, so I hope I'm not retreading old ground. I decided to use a set of PP slides as the basis for filling out a multipage A4 pre-printed form. I used images of the printed pages, scanned directly into PP and drew textboxes in the appropriate positions. Then I deleted the images and printed my text etc. directly onto the preprinted form.

It worked well for the most part, but the text (boxes) consistently needed to be dropped down by about 1 line height of 10pt Tahoma font, and shifted right by about 1 character width of the same font. I'm wondering if there's something I can do to ensure "perfect" alignment in the future, since this looks to be a useful general method of doing this kind of thing.


Hello everybody!

I've got a little problem (at least I hope it's little). First of all I have to explain I'm not a native speaker and am not working with the english but a german version. So it could be I don't know the correct technical terms to describe what I mean. Nevertheless I hope to make myself understood...
So here's the topic: I want to activate Macros on mouseclick on an object (s.th. like hyperlinks or starting another application). When I click the mousebutton I want a little graphic to pop up, and on another click to disappear again. All this should happen during the running presentation...
Is there the possibility to do s.th. like this? If so I would appreciate a little example.
I know that I could realize this example also by using animation settings, but this one is not the real idea, just an attempt to get accustomed to the general process.
Many thanks in advance!!

Page 49 of 50.
Results 961...980 of 2,428