Replies

look up the VBA or VB help file for filescriptobject then search for .rename

Thx for the ‘good oil’ Cheerz.

I’m not anywhere up to speed with this openprocess & getexitcodeprocess stuff but I’m assuming that it is windows stuff. Is that right? Is there a good reference where I can read up on this?

Also, the loop while I wait for the compile to finish – doesn’t that chew up processing capacity?

Tim

Thanks Charlotte, I knew about the File, Make command.

It seems that when I try to minimise the content of my ‘post’, the only thing that happens is that I get mis-understood ( must be a life lesson in there somewhere) and have to provide greater details down the track.

The full (?) situation is that I want to use line numbers in my error handling routines. There are programs available that add line numbers, move the source code to a read-only directory and compile the project all in one go. However, you have to pay for them.

Now, me, not wanting to hand over money for something like this (I would prefer to go to the movies), decided to write my own program. When I run it, I select the VB project that I want line numbers added to and click on ‘run’. This then adds line numbers to the selected project, copies the selected project to a sub directory, makes the files are read-only. Only one step missing, compiling the selected project.

So, in short, I want a program that copies a project and compiles it but I’m not sure of the syntax for the vb6 call.

I hope that that is clearer.

Tim

I would have thought that round(rand()*23,0) would not produce a uniform distribution of integers between 0 and 23 as 0 and 23 would only appear about half as often as the other numbers.

I’ve always used int(rand()*24) + 1 to get a random integer between 1 and 24.

In short, you need some form of bubble sort based routine. To illustrate, suppose you have 4 words – call them 1, 2, 3, 4. Then you can have a total of 24 different combinations – 4 x 3 x 2 x 1 = 24. If you only want a output length of 2 words then you have a total of 12 combinations (4×3).

You need to think in terms of an array of length n with each word held in one element of the array. Thus we have an array with n words (ie n elements). Each element is referenced by an index (1 thru n).

Now you want a function that takes the kth (where k = n-1 to start with) index value (in the above example ‘3’) and increases it by 1 (checking that that index value is not used in positions 1 thru k-1. If it is, then increase it again and check again. If the value of the kth index > n then go back one index value (ie k = k-1) and try to increase that value. If the function cannot increase any index value, then you should have reached the end.

Once you have successfully increased the kth index, reorder the k+1 to nth index in ascending order and output the result.

Then call this function again with the new array that you just formed. Stop calling the function when you get an array of n, n-1, n-2, …, 3, 2, 1.

Clear as mud? Consider the following example:

1234 -> into function yields 1243 (output result and use this value as the new input into the function)
1243 -> into function yields 1324
1324 -> into function yields 1342
1342 -> into function yields 1423
1423 -> into function yields 1432
…
4312 -> into function yields 4321
4321 -> function cannot increase any index

This is a toughie. I had the same problem and had to write a macro to re-do the path. The easiest way is to record a macro as you set it up and then examine it for the details that you want to change. Put it the new path and use the macro to edit the connection.

There are two path type connections (from memory), one that deals with the location of the file and one that deals with the name of the file (includes the path for some reason!!).

I have no idea about Office XP – sorry.

Tim

Hi Joe,

The attached file contains the macros. You will need to copy them to the macro section (I’ve just put them in the sheets).

Its not the prettiest macro but it does do the job – I have yet to write the all singing – all dancing macro – maybe next year.

Cheers,

Tim

I’ve got a few spreadsheet that do some complicated stuff and need some form or ‘Don’t worry, I’m still working’ note to the user. You can create a progress form and display it modeless (or is it modal?). Use something like this …

frmProgress.Show 0

and the code keeps progressing. Then in your code you can update the frmprogress with something like this …

frmProgress.lblProgress.Caption = “message”

You might need a repaint commant too. You can also do this in excel 97 – there was an article in the knowledge base that showed how do do it but i’ve forgotten the number and cannot find it now

Cheers,

Tim

you learn something new everyday.

Thanks a lot.

you could use …

int(YYYYMMDD/10000) to return the year
mod(YYYYMMDD,100) to return the date
int(mod(YYYYMMDD,10000)/100) to return the month

to three separate variable and then go from there.

try …

Dim i As Integer
Dim fso As Object
Dim lsFileName As String
Dim lvFile As Variant
Dim lsPath As String
Dim lsNewFileName As String

Set fso = CreateObject(“Scripting.FileSystemObject”)

lsPath = “C:Directory
lsFileName = Dir(lsPath & “*.*”)
If Not lsFileName = “” Then
lsNewFileName = ‘formula based renaming method’ End If

Do Until lsFileName = “” ‘ get the file and copy it
Set lvFile = fso.getfile(lsPath & lsFileName)

‘ rename file
lvFile.Name = lsNewFileName

‘ get next file name
lsFileName = Dir
If Not lsFileName = “” Then
lsNewFileName = ‘formula based renaming method’
End If
Loop

Thx Vicadin for posting the solution to this prob. I got a very similar error message using Access 2000 – “no control in this form” on the same dialog you described.

Using your solution as a template, I looked in the register and noticed that it was looking for this dll in the wrong directory. Simply solution of pointing it to the correct directory and … bam … problem solved.

Again, Thx you so much for posting solution.

Cheers,

Tim

Hi All,

I currently have Office 2000 installed as my base application. I am developing macros that have to run in excel 97 as well as 2000.

Firstly, are there any things other than forms that I should steer away from?

Secondly, I want to install excel 97 on my machine as well as have excel 2000 installed. Any suggestions regarding the best method of doing this?

I’ve found this …

Q214388 – XL2000: Running Multiple Versions of Microsoft Excel

… but does anyone have any first hand knowledge?

Cheers,

Tim

I think that you should leave the space in. The bit before the space is know as an region. As an example, take my work post code KT17 4RS

The KT represents an district – Kingston Themes.
The KT17 represents a region.
The KT17 4 represents an area.

Clear? No not very but that is the british way for you.

Cheers,

Tim

Thanks all for your replies.

I really wanted a form that would only return the selected path – not process any code. I could have put all my processing code in the cmdOK button but that would make the form hard to use for other purposes.

So I kept all my processing code in a sub and wanted to know if the user had selected to cancel so I could skip to the end of the sub. Sounds like the solution I used is ok (it works).

Again, thx for everyone’s replies.

Cheers Tim