Posts by A9192Shark

Re: Identifying the username that is locking a file for editing

This sounds more like a network question than a VBA one. You are actually asking a question of the Network and not the file.

I am sure that the Network admin could find out who has a network file open. You would therefore need to be able to access the Network file system and this would depend on your networking software.

I doubt that VBA will be able to access this information without permission from your network administrator to link to a library of some sort.

Good luck,

Alan.

Re: Identifying the username that is locking a file for editing

I may have missed something here but I thought you wanted to be able to ask the person with the file open to close it and therefore you needed to know who had it open.

Quote from pdsasse

We're looking for a way to determine the username of the person locking it. Not the username as it appears in Excel but the network login name of the user so that we can contact them and ask them to close the file if they are done with it.

The code that appears to be suggested here will either close the workbook after it has been open for a period of time or close it after a period of inactivity.

What if I am in the middle of using the file and keep using every 5 minutes for the next 5 hours? You will either force the close (I think you have rejected that code) or will never get to know who has the file open.

I apologise if I have missed the post that changed the objective,

A.

Re: Identifying the username that is locking a file for editing

An alternative solution is to simply have a hidden sheet that stores the username when the file is opened and deletes it when the file is closed. If the file is already open you could display a message box to say that it is already opened by another user.

Insert this code in the Workbook code sheet

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("Hidden Sheet").Range("A1").Clear
End Sub


Private Sub Workbook_Open()
    If ThisWorkbook.ReadOnly Then
        MsgBox "Already opened by: " & Worksheets("Hidden Sheet").Range("A1")
      Else
        Worksheets("Hidden Sheet").Range("A1") = Environ("Username")
        ThisWorkbook.Save
    End If
End Sub

This little peice of code will display many environment variables, I guess that what you want is either NWUsername or Username. (Code taken from http://www.tek-tips.com/faqs.cfm?fid=4296])

Option Explicit


Private indx As Long


Sub List_Environment_Variables()
Dim EnvString As String
indx = 1
Do
    EnvString = Environ(indx)
    Cells(indx, 1) = EnvString
    indx = indx + 1
Loop Until EnvString = ""
End Sub

A.

Re: Excuting SQL select statement in VBA

or DoCmd.RunSQL

If you want to run an action query then you will probably want to turn the warnings off, I use the following two procedures to do this:

Public Sub mp_ConfirmOff()
    Application.SetOption "Confirm Action Queries", False
    DoCmd.SetWarnings False
End Sub


Public Sub mp_ConfirmOn()
    Application.SetOption "Confirm Action Queries", True
    DoCmd.SetWarnings True
End Sub

A.

Re: Move nodes up down in a Tree view Contro

If you wish to move a branch up or down the tree then just change the key of the nd.Parent property.

Say you wanted to move a node with key "MoveMe" to be a child of a node with a key "MoveToHere" then the code would be something like:

tv.nodes("MoveMe").Parent=tv.Nodes("MoveToHere")

Hope that helps!

A.

Re: Inserting text in HTML/text file

Wigi,

Do you follow the VBA in the workbook?

The code basically:
1. Opens the html file and a temporary text file
2. Reads each line of the html file until it finds the searchfor text, writing each line that it reads out to the temporary file.
3. When it finds the searchfor text it outputs the text that I want to output
4. Resume reading the file and writing out each line.

What you will need to do is modify item 3 such that instead of writing out my text it writes out what you want. Then start reading text in until it finds the second search for phrase and only then start writing out.

I am unlikely to get a chance in the next two weeks to answer any questions on the board as I am going skiing next week (hooray) and have a ton to do before then!

Hope the description above helps.

A.

This post is a response to a question I posted on an MS Forum. I have asked Dave if I can place the answer here as it requires a workbook and there is no facility to post a workbook on the MS Forum.

Although the question relates to html files produced by MS Publisher the workbook may of use to others who wish to insert text in a text file.

If you have arrived here from the MS Forum then enjoy this excellent forum!

Quote from "a9192Shark summary of discussion on MS Forum"

Using MS Publisher it is not possible to enter an HTML code fragment into a header. If you need code in teh file then sometimes you can post it in the body, but some code must be in the header. The only way to do this is to open the html file and copy and paste each time. However this becomes very frustrating if you keep rebuilding the files.

The attached workbook will allow the user to search through html files and insert text after specified text. This has been used with success on my (Alan Forster) web pages. I may improve the workbook over time as I require and will post different versions.

Note that the workbook was developed for my specific purpose and is very 'quick and dirty'. It may require some modification for your purpose, it is therefore provided without warranty!

Good luck and I hope that this helps,

A.

Re: Select search criteria from 3 combo boxes

Robert,

All of the records should be displayed if the filters are all null.

However, I think what you are saying is that the user MUST pick something from each combo box before the filter will be used. In that case your initial code will work, but could be improved slightly.

If strFilterCode <> "" And strFilterDescription <> "" And strFilterBand <> "" Then 
        strFilter = strFilterCode & " AND " & strFilterDescription & " AND " & strFilterBand 
    Else 
        strFilter = ""
    End If

This would only create a filter if all three combo boxes are not null.

A.

Re: Select search criteria from 3 combo boxes

Robert,

The problem will be in this part of the code. What this says is:

If I have a value in all three combo boxes then do something, else do something else.

However the something else does not do something sensible.

If strFilterCode <> "" And strFilterDescription <> "" And strFilterBand <> "" Then
        strFilter = strFilterCode & " AND " & strFilterDescription & " AND " & strFilterBand
      Else
        strFilter = strFilterCode & strFilterDescription
    End If

Assuming that you can pick 0-3 of the combo boxes then you need to code for all possible scenarios.

You can use a case statement or simply a conditional build (note that some do not like the use of IIF() as it is a 'slow' function. In this case I suspect you will not notice the difference.

StrFilter = ""
If strFilterCode <>""  then strFilter = "(" & strFilterCode & ")"
if strFilterDescription <>"" then strFilter = strFilter & iif(strFilter<>"", " AND (","(") & strFilterDescription & ")"
if strFilterBand <> "" then strFilter = iif(strFilter<>"", " AND (","(") & strFilterBand & ")"

Does that help?

A.

Re: using replace in string value

Boromuse,

When you say it does not work, what error do you get? Or does it execute but not replace the characters you expect?

Also, check to see if Replace is a function in 97, not sure if it was there or not. Also you do not need the final 1 in the replace statement since this is the default value for the optional parameter.

A.
PS I suspect someone will point youin the direction of code tags- you might want to do it before being told off!
A.

A.

Re: trendline gaussian

Benj,

Slow down. I think that you need to see the different shapes of teh functions that you are considering. Then you will undersstand when I suggest doing a particular transformation why I propose it.

Have a look at the attached workbook, it shows the pdf and cdf for a family of curves for the Gamma and Normal distributions/functions.

Now we need to work out what shape we think your data curve is....

Once we have this tool then you can start playing with the parameters to get a best guess. Then we can put these best guesses into a model like Oil.Zip.

HTH,

Alan.

Re: trendline gaussian

Benj,

Now we are getting somewhere. What we have is the curve for the CDF (cumulative distribution function), but what we want is the pdf (Probability Distribution Function). Fortunately Excel provides both functions.

What we can now do is one of two things.

1. Discretise the data into bins such that we build a histogram of the pdf and try to fit that curve. That was the curve fitted in Oil.zip

2. Avoid introducing errors in our analysis and fit acdf and get the parameters and then use that to generate the pdf.

What we need to do is have a look at the cdf for a variety of functions and plot them for a variety of input parameters (known as a family of curves). You did this (?) for the GammaDist function, although we plotted the pdf, not the cdf.

Try to create a workbook that has a graph of both the pdf and cdf for a theoretical dataset using the Weibull, NormalDist, GammaDist, LogNormDist functions. Then we can look at which one we actually need to use.

Post the workbook back as a zip file when you have done this.

A.

Re: trendline gaussian

Benj,

Are you familiar with using $ signs in formulae?

The formula that you will need will be something like
=CountIf(A$1:A$3772,"<="&A1)

Do a help search for "Paste Special", it is on the Edit menu.

A.

Re: trendline gaussian

Benj,

I am just about to leave the office.

To implement the things in my previous post.
1. In the column adjacent to your data use the formula CountIf to find the number of records that are less than each data point.
2. In the next column put a formula that calculates the % (a number between 0 and 1) of the total number of records that are below that data value.

3. Copy all three columns and paste special values
4. Now sort all three new columns on the %
5. Plot a chart of the data using the sorted raw data as the x and the % as the y data.
6. Change the x axis scale to be a log scale.

Now we have a graph of the data.

If you are not familiar with doing this then please say so.

We now need to fit a curve to your data.

You have previously stated that you need to fit specific curves. Is this the case or are you trying to find the curve with the best fit?

I am out of the office for a few days.

You need to read your stats book on fitting continuous distributions. If you do not have this in your book then find one from the library that does. It is first year degree statistics.

Good luck,

Alan.

Re: trendline gaussian

Benj,
It would help me (and others) if you added to your profile your level of experience with Excel and where you are in the world.

Thanks,

A.

Re: trendline gaussian

Benj,

I have had a quick look at your data.

I am not familiar with the specifics of the "Operational Risk Loss Distribution Approach" but a quick Google has shown me that the data you have is probably the output from some form of stochastic risk model. You are being asked to fit a curve that shows the distribution of risk and the probability of a given cost being exceeded.

By adding columns to the data that count the number of records less than each value then copying these counts and the raw data to new columns and sorting on the counts you can get an estimate of the cumulative curve. Plot the values on the x-axis and the counts on the y-axis and you will see a curve that is not very pretty. Then log the x-axis and you will see a nice smooth s-curve. This immediately suggests that we are looking to transform our data using a logarithmic function. Note however that since the data includes some negative values we will need to adjust the function to deal with these.

Can you get this far?

A.

Re: trendline gaussian

Benj,

Please post the data that you have so that I can advise you.

The scaling was undertaken by multiplying the data by a value. You asked questions on it in an earlier post.

Please post your data so that I can help you. If you do not post the data I will find it very difficult to guide you in teh right direction.

Thanks,

Alan.

Re: trendline gaussian

Benj,

In the case of the Oil production we wanted a curve that was 'bell shaped'. Have a look at teh attached sheet that shows four different Gamma Dist. All are possible and all could apply, but we wanted something like the first or second.

Now look at the values on the axes. The x-axis is generraly below 30 and the y-axis are all below 1, infact closer inspection below 0.5 (although this can change).

So the Oil data had x-values between 1850 and 2050, these need to be transformed to 0 to 30.

The Production numbers were in millions so I scaled them to give me a smaller number in the range for the Gamma Distribution of the form that I wanted.

These steps are known as transforming your data. (I suspect that you will not have to do this).

Now I sit and play with the a and b values till I get roughly the right shape and then let solver get the best fit.

However, if you have read the whole thread you will see that I stated early on that this was not teh best approach. Assuming that you are studying statistics at either 'A' level (High School/Junior College?) or degree level then you need to make sure that you are answering the correct question and using the correct methods.

If you are really stuck go and ask your teacher lecturer to explain it again to you. Take with you what you have done, including the print outs from the site I gave you and the workbook attached and your text book (have you got the recommended text book?). Remember, the chances are that the teacher/lecturer loves this subject (even if you don't). Asking someone about what they love is a gift. They will almost certainly be willing to spend the time with you.

You could spend hours asking us (and we will continue to try and help) but you might only spend 30 minutes with a teacher and have it all sorted out!

If the teacher will not help then please post the actual question (in full) and data and I will see what I can do to help you solve it.

Good luck,

A.

Re: trendline gaussian

I had a feeling that this was homework....it is useful if you state this upfront. Not because we will not help you, but because it changes how we explain things and what we will get you to do.

OK. Have a look at this site and it will provide a description of the gamma distribution.

You can see that the parameters change the curev significantly.

I have to go now, but will have anotehr read in the morning.

A.

Re: auto select listbox value

Sounds to me that there is some confusion here.

If your list box only has one value why are using a list box? Could you not use a text box?

When do you want to evaluate the listbox (or text box)?

If there is already code running that populates the list box or forces it to update then surely this code could be used to determine if the command button is visible.

Now I am guessing.
Do you have some form of filter on the form that updates a sub form, but you only want the command button to be visible if there are records in teh sub form? If so have a look at the Has Data property of the sub form. Then when you update your filter simply test teh hasdata property and show or hide the command button.

HTH,

Alan.