For anyone who has a little spare time...
For anyone who has a little spare time...
(OP)
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,
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
RE: For anyone who has a little spare time...
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.
RE: For anyone who has a little spare time...
RE: For anyone who has a little spare time...
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
RE: For anyone who has a little spare time...
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".
Help me out of this... please!
frag
patrick.metz@epost.de
RE: For anyone who has a little spare time...
I never figured out how to make it work. I actually ended up leaving the progress bar out.
Kevin
slanek@ssd.fsi.com
RE: For anyone who has a little spare time...
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.
RE: For anyone who has a little spare time...
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
RE: For anyone who has a little spare time...
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
RE: For anyone who has a little spare time...
Kevin
slanek@ssd.fsi.com
RE: For anyone who has a little spare time...