For anyone who has a little spare time...

[KevinFSI]

Take a look at this code. It's a very basic progress bar running along with a macro that pulls info from one wookbook into another and formats it a certain way.

PROBLEM: This works great on my machine, but when I gave it to the girl who was going to be using it, it didn't work. It failed at the very beginning and immediately highlighted the line that says newHour = Hour(Now()) If I remove all the code that deals with updating the progress bar, it works fine on her machine. The error says something about "...can't find required library or object..." or something of that nature.

QUESTION 1: Of course I want to know why this thing won't work with the progress bar on one machine, but will work fine on another.

QUESTION 2: I know that I've probably chosen the worst possible way to update the percentage displayed on the progress bar. Any suggestions there?

And here's the code ladies and gentlemen:

Sub EmpNum()
'
' Macro8 Macro
' Macro recorded 11/3/2000 by Kevin A. Slane
'


    'Update progress bar
    PctDone = 0
    Call UpdateProgress(PctDone)
    
    Workbooks.Open Filename:= _
        "\\blah\blah\TimeEntry.xls"
    Windows("TimeEntry.xls").Activate
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    Range("C2:C652").Select
    Selection.Copy
    Windows("Vacation_Data.xls").Activate
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    Sheets("Sheet2").Visible = True
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    Sheets("Sheet2").Select
    Range("A1").Select
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    ActiveSheet.Paste
    Sheets("Sheet1").Visible = True
    Sheets("Sheet1").Select
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    Range("B2").Select
    Application.CutCopyMode = False
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    ActiveCell.FormulaR1C1 = _
        "=IF((AND((LEN(Sheet2!R[-1]C[-1])=5),(NOT(ISBLANK(Sheet2!R[-1]C[-1]))))),(Sheet2!R[-1]C[-1]-60000),IF(ISBLANK(Sheet2!R[-1]C[-1]),"""",Sheet2!R[-1]C[-1]))"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B652"), Type:=xlFillDefault
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    Range("B2:B652").Select
    Range("B2").Select
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    Windows("TimeEntry.xls").Activate
    Range("I2:I652").Select
    Selection.Copy
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    Windows("Vacation_Data.xls").Activate
    Range("C2").Select
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    ActiveSheet.Paste
    Range("C2").Select
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    Range("D2").Select
    Windows("TimeEntry.xls").Activate
    Range("J2:J652").Select
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Vacation_Data.xls").Activate
    Range("D2").Select
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    ActiveSheet.Paste
    Range("D2").Select
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    Range("D2").Select
    Windows("TimeEntry.xls").Activate
    Range("H2:H652").Select
    Selection.Copy
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    Windows("Vacation_Data.xls").Activate
    Range("E2").Select
    ActiveSheet.Paste
    Columns("C:E").Select
    Selection.NumberFormat = "0.00"
    ActiveWindow.ScrollRow = 1
    Columns("C:E").Select
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Windows("TimeEntry.xls").Activate
    ActiveWorkbook.Close
        
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    Windows("Vacation_Data.xls").Activate
    Sheets("Sheet2").Visible = False
    Range("A1").Select
    Sheets("Sheet1").Visible = False
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    ActiveWorkbook.Save
    
    'Update progress bar
    PctDone = PctDone + 0.05
    Call UpdateProgress(PctDone)
    
    'Update progress bar
    PctDone = 1
    Call UpdateProgress(PctDone)
    
    'Pause for 1 second
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Unload UserForm1
    
    Shell "MSAccess \\blah\blah\vacation_app\vacation", vbMaximizedFocus
    
    
End Sub


Sub UpdateProgress(Pct)
    With UserForm1
        .FrameProgress.Caption = Format(Pct, "0%")
        .LabelProgress.Width = Pct * (.FrameProgress.Width - 10)
        .Repaint
    End With
End Sub

The code is pretty darn slpooy, but it works. I know there is a better way to change the percentage on the progress bar, but don't know how. This is for anyone who is bored, can't sleep, is on a coffee high, etc...

Later,
Kevin
slanek@ssd.fsi.com

 

[frag]

Hi Kevin!

First of all: No i can't solve your problem!

You said somthing about an error-message ("...can't find required library or object..." ).
I have no idea about this progress bar but it sounds like the progress bar is not a standard component of Excel(or VBA).
I think that you have to copy the file (library, object, class, or whatever) which includes the progress bar to the girl's machine where your macro is meant to run on.

 

[dsi]

I agree with frag. Check the project references and make sure to include all of the non-standard object libraries with the project.

 

[KevinFSI]

The progress bar is a simple form I made using the form builder in VBA. Nothing special.

Shoot me an e-mail if you want and I'll send you guys the app. You don't have to do anything with it, but you can see the bar is just a form. Kevin
slanek@ssd.fsi.com

 

[frag]

Hi Kevin,

this if frag again!


I don't know if you already have solved your problem (you wanted to send me a mail).

Now I've got a very similar problem!!

I am using the standard VBA-functions Mid() and Date() in Access97 on NT machines. The only machine where my code runs is the machine on which I have developed the programm. On nearly all other machines I got compiling-errors (Can't find project or library).

I guess that there are missing some dll's... but I have copied all dll-files from my machine (the good one) to another machine... no effect. I used the files from "C:\Program Files\Common Files\Microsoft Shared\VBA".

s-) I am really running mad on this!!! X-)

Help me out of this... please!

frag patrick.metz@epost.de

 

[KevinFSI]

Hey Patrick,
I never figured out how to make it work. I actually ended up leaving the progress bar out. Kevin
slanek@ssd.fsi.com

 

[tedsmith]

Frag: Did you package it all up and install it properly or just copy the exe file?
If the former you have to make sure you include all the DLL references needed when making the distribution disk.
If the latter, you wont have installed the DLLs needed.

 

[frag]

Hi tedsmith!

I didn't package it and I have no exe file. (By the way: How the hell do I package an Access97-database or get an exe file out of it ?!?) It is just the mdb file with to forms in it! I want to put the mdb file on a networkdrive because serveral people have to use it at the same time for storing data in the database. That's all.

cya

frag patrick.metz@epost.de

 

[frag]

TO KEVIN AND TEDSMITH!!

This is frag with some news:

Tata... I DID IT! I solved my problem! How? Here we go (believe it or not):

I went to the machine where my code didn't want to run and opened two(!) Access applications. I opened my database in both apps. Then I kicked all forms out of one application and copied the form objects (radio buttons, textfields, etc.)in formwizard mode via drag and drop to new (empty) forms in the second app. Then I copied the whole code from app 1 to app 2. Save and done! Now it workes on all machines. Don't ask why!!

Perhapse Kevin should try this with his status bar too?!

cya

frag *very happy* patrick.metz@epost.de

 

[KevinFSI]

No kidding? I guess anything is worth a shot! Kevin
slanek@ssd.fsi.com

 

[tedsmith]

In the Access2 there was a developers program that you could use to produce a runtime royalty free program so you did not have to have a legal copy of Access in the computer. I presume the is an update for Access 97 that will do the same? You then had only the data in the mdb file which you attached to the main program. This makes it much easier to update the forms, queries and reports without affecting existing data.