Posts by Michael Hayes

Re: NUMBERS in EXCEL

The times are held as decimals (of a day) and can readily be added and subtracted. It is only the display of those numbers that is formatted for hh:mm:ss
I often have a four or six character text field so you can just enter half past 1 in the afternoon as 1330 then separate this with text functions to get it to 13:30:00 then do your calcs and of course displaying as a 4 or six figure time field is fine. You just want to avoid having to enter the colon when inputting the time.

Re: File too Large

Thanks for your reply, I had not picked this up. You are quite right.

Re: File too Large

Sorry for the false start, it is all in the vestigial formatting in columns k to xfd, delete the formats there and the problem disappears

I have a workbook which was very large and have tried to identify what is causing this.
I found there were many sheets, all "look" the same but some were 10kB and some were 500kB which I determined by systematically deleting sheets, saving and looking at the size.
One of the large sheets was copied to a new workbook and it is still large. I have deleted all orphaned links, merged cells, cell borders and even the data and it is still 464kB
There are no Hidden sheets etc the original data occupied A1:J104 I think and selecting ALL columns then all row beyond this range and deleting made no significant difference.
If I copy say row 120 over the top of the first 104, it drops to a few kB but I am at a loss to see what is there. Can it be the ghosts of data deleted? do I need to clear an undo cache?
It would seem that a lot of the excess junk exists between row 60 and 40 with virtually nothing below row 40.
Can anyone explain this and more importantly, give me an idea of how to fix it? Obviously without destroying the original data or hopefully the original format.
I am posting an "EMPTY" work book as above
forum.ozgrid.com/index.php?attachment/71221/

This spreadsheet started life 18 years ago as a "Directory Listing in a Spreadsheet" which I posted on this forum back in 2004. Last year, I finally upgraded it to firstly find all .mp3 files in and below a starting directory and to then extract as much data as I could from the file. Particularly, the bit rate and to then determine the playing time of the track, two aims that proved more difficult than I expected as they are not readily accessible in a .mp3 file. Of course if the file is Variable Bit rate, these two will not converge to a simple value. I would like to acknowledge the source information I used to achieve the final spreadsheet but this information only exists on a computer I no longer use. Various aspects of the code may have been borrowed from many sources but again the specifics are long lost but the concept is original and stitches the above as a means to an end.
To use it, copy a starting directory from windows explorer and paste it to cell A1 on the sheet Data. Then press the Run Button. Once all sub directories and files have been collected, press the mp3 button to open each file and extract data from the file.

Re: Cell Referencing

Hi Tom,
I would go to where the value of CPI is entered and Name That Cell as CPI (Select Cell, right Click and Define Name as CPI) then use a find (CTRL+F) to find CPI and replace it with =CPI and it will simply convert your cells to an equation which will hold the value you need, even the ones that just have CPI in them.
Cheers,
Michael

Re: Byte Rotate

Cytop,
Thanks very much for the code. It makes perfect sense. I think when I tried dividing, I had not used integer division and had even forgotten about this.
Cheers,
Michael

I am using some Byte data and can easily bit pick individual bits of the data but I can not see how to Rotate or Shift the bits (left or right). I know this is a little below VBA but would appreciate any heads up on a way to do it.
Thanks,
Michael

Re: Before Opening a File, Trap if access is not available

Thanks for the replies. It has been a curly one because you are left with no-where to go when a vba method just doesn't work.
I tried it many times since and the results are "random" obviously not random but the thing that varies is neither known nor visible to me.
In the end, I found that using

Application.Displayalerts = False

was the thing I was looking for, it allowed me to suppress the error dialogue and use OnError to handle it myself so the final solution which I have now tested across the range of access points is

Application.DisplayAlerts = False
    On Error Resume Next
    Workbooks.Open Filename:=Path & FName, Notify:=False
    If Err <> 0 Then
        Err = 0
        Application.DisplayAlerts = True
        GoTo Sortie
    End If
    Application.DisplayAlerts = True
    On Error GoTo 0

Thanks again,
Michael

Re: Before Opening a File, Trap if access is not available

Anthony,
You are quite correct. The file is one of many held in Sharepoint. I fully agree, the code you published should be exactly what I want to do. I had started down the path of trying to make something work by plugging the holes. Part of my problem is that I have only seen the problem from a few perspectives.
I am a Sharepoint administrator and have no problem from a our work network. I sent the file home and found

From my home computer, I do not have access and get the wanted error message
From my work Computer (offline at home), I do not have access and get the wanted error message
From my work Computer (connected to Internet), I do not have access and get the wanted error message
Then I establish a VPN connection to work and expect to get in but find, I do not have access and get the now un-expected error message
Next I opened the Sharepoint Site and looked at the file in Internet Explorer
I then tried again expecting for sure I would have access but found, I do not have access and get the now even more un-expected error message
When I returned to work this morning, and tried from our network with exactly the same file as last night and that worked yesterday afternoon, I do not have access and get the Most un-expected error message

With no reasonable explanation, it started working both for myself and for another user an hour or so later

When I sent it to a third person, (after the usual lowering of macro security etc) I found he did not have access and this is good because I had not enabled him. When I did, and waited half an hour for any latent permissions to filter through, he still did not have access. After I drilled down to the site and looked at the file on his computer, I then tried again and it worked.

It is almost as though Excel is working with a cached set of permissions which is in consistent with the command which is specifically supposed to check the current state of the file, ie, does anyone else currently have it open?

I realise this all makes it a cross application problem but the fundamental problem remains, irrespective of whether it is a Sharepoint site or a public folder, I would like to try and open the file and if it Bombs out, take control of the error handling before I let the Native Excel error handler broadcast the problem.

Regards,
Michael

I have a spreadsheet I am trying to restrict to within the company. I would like to neatly handle an error, where someone who does not have access to the file location, or who is using it outside the company, gets a simple warning of this. Innitially I tried opening the file and trapping any errors generated but Excel took over before I could get to the error and revealed the file's location.
I have tried the Cancheckout method which is giving intermittent results. The code I have used is as follows

On Error Resume Next
    If Workbooks.CanCheckOut(Filename:=Path & FName) Then
        If Err <> 0 Then
            Err = 0
            GoTo Sortie
        End If
        Workbooks.Open Filename:=Path & FName, Notify:=False
    Else
        GoTo Sortie
    End If

My understanding is that the Err checking should not be needed as the

Workbooks.CanCheckOut(Filename:=Path & FName)

should return a Boolean to determine if access is possible but I found it was giving untrapped errors so this simply filters out any that get through. I find that I have had Null, True and Error messages as possible outcomes of the line in question. In a microsoft example they check if the "Boolean" result =TRUE and I am at a loss to understand why this would be necessary.

Has anyone had experience with this method or is there a suggestion for an alternate method to give a simple "No Access" message and then terminate the code?

Having read the suggested threads, there is one from Dave which appears to acheive the same outcome, I am not sure then if the CanCheckOut is a new method or perhaps not being used correctly, any advice would be appreciated.

I adapted the method given and tried it, I get the same outcome as my original approach, an Excel message which reveals the full location before I get to trap the error so no solution as yet.

I am working in Excel 2010 on Windows 7

Re: Securing data in a worksheet table

Thanks Dave

Re: Securing data in a worksheet table

Dave,
Thanks for the suggestion. I have experimented with VeryHidden worksheets but it is too easy to toggle the state. I would like to lock it down under password and thought I had this licked this morning but without going into details, I found it very easy to bypass this and get to the data. I guess I am looking to lock the data to a reasonably high power user but not to password hacking. I am rapidly coming to the conclusion that if the data is sensitive then the answer is to not put it in excel but I am hoping there is another point of view.
Regards,
Michael

I have a number of tables in a spreadsheet which form the basis of a complex lookup. I am seeking a way to prevent a user form accessing these tables directly. What is the best way to keep these hidden?
Michael Hayes

Re: Directory Listing in a Spreadsheet

hmorris,
Pleased to hear it is helpful. I still use it extensivley myself.
Your question prompted me to have a closer look at this because I have had the same problem.
I tested by creating, modifying and then accessing a file and then checking it properties by right clicking. I found that the Created and Modified reported correctly but the Accessed reverted to the saved time stamp. The three possibilities I had to chose from for the listing were
DateCreated, DateLastModified and DateLastAccessed and I chose Modified and accessed. You could replace one of these or add the created if you wish.
From your post, you are getting different behavior where the Modified stamp is changed by opening a file. I am not sure if this is a system setting ???
I would be very pleased to hear of any further testing you do along these lines.
Regards,
michael

Re: click one cell will generate result/name problem.

zarboga,
very easy to transfer. First set up your new sheet with the text, labels and lists you want. Then go to the example, right click on the sheet tab and select view code, highlight and copy all the code. Do the same on the destination sheet and paste the code, same as copying a block of text from one file to another.
If you are playing with this and the code stops running, it is most likely because the code was stopped before events were again enabled. You can either write a one line routine which enables them or you can open the Immediate window and copy the line of code

Application.EnableEvents = True

and paste into the immediate window and press return to execute it
Regards,
Michael

Re: click one cell will generate result/name problem.

zarboga,
you can do this with code attached to the sheet rather than held in a module.
To see the code, on the sheet tab, right click and select view code. If you put a Breakpoint in you can then single step through the code to see how it works by selecting a cell in the spreadsheet.
When you select one of the Cells C6 to C8, it will find the label in the cell to the left in the row starting at cell G5 and then transfer the contents lying below this label to the range starting at cell D6 as you require. When you select any other cell in the sheet, it clears from D6 down. Beware that it can also clear any data below cell D6. You might like to place some limits on this. There is also a simple check to not try and transfer more than 100 cells back, most likely caused by putting a heading at say cell J5 but with no names written below it. To expand,
simply type a new label staring at b9 and write the words "click to see" in the adjacent cell and then expand the columns you have hidden with the sport and names.
If you select a range of more than one cell, it will detect this and will not try and match the data.
Regards,
Michael

Re: List Files In A Folder In A Worksheet

Hi kilimche,
The link posted in my original reply will list all subdirectories and files from the given path down to the last file.
Regards,
Michael

Re: Cannot use ISERROR to enclose this formula

Hi,
Just about to hit the go button and realised I had totally missed your question. You could try...

=if(iserror(IF(NOT(LEFT(B8,3)="RTP"),B8,MID(CONCATENATE(E8,F8),SEARCH("P20",CONCATENATE(E8,F8),1),13))),"Place error text here",IF(NOT(LEFT(B8,3)="RTP"),B8,MID(CONCATENATE(E8,F8),SEARCH("P20",CONCATENATE(E8,F8),1),13)))

This is not all that efficient as it has to evaluate the expresion twice. If you serach for use of the ISERROR I am sure there are many posts on better ways to go but at least this works. To get it, just highlight your equation to the right of the = sign and press <Ctrl+C> and then type

=if(iserror( <Ctrl+V> ),"Place Error Text Here", <Ctrl+V>)

Regards,
Michael

Re: Reference workbook in VBA using cell value

Jack,
If you single step your code and hover over the variable Cashbook or use the immediate window you may find it is empty. Try the following in place of what you have as an indirect reference to the contents of the named range, or build the concatenation directly in the code.

Windows(Range("Cashbook").Value).Activate

Regards,
Michael