SpeedDemon
10-06-2007, 11:03 PM
I'm trying to make my program work in Excel version 2007. I open the file in Excel 2007, and try to save a copy with the SAVE AS command as a macro-enabled file-type, but something goes wrong. When I save the file that way, and when I run the new copy, the program is supposed to copy the worksheet as many times the user wants, but when he starts copying the worksheet he can't find some hidden file, and he gives me this error message: Path not found: '.\VB28.tmp'.
When I open the root directory with Windows Explorer, i find that hidden file exactly beside the Excel file. Why can't he find it then????? The file is right there beside it!! And the file is visible, and not hidden. And immeditely after that he giives me another error message: Run-time error '1004': Copy method of Worksheet class failed. This is the line invoking this error: Sheets("1").Copy After:=Sheets(i - 1)
The macro works fine in the original file, but as soon i copy it or save it with the save as command, it looses it's link to some excel hidden file.
Here is the entire code:
Private Sub Workbook_Open()
Dim brojProstorija As String
Dim i As Integer
Dim hk As String
Dim zarez As Integer
Dim lijeviDio As String
Dim desniDio As String
Dim duzina As Integer
Dim boja As Integer
Application.WindowState = xlMaximized
Application.CellDragAndDrop = False
Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlToRight
Worksheets("1").PageSetup.BlackAndWhite = True
Worksheets("Rekapitulacija").PageSetup.BlackAndWhite = True
Application.EnableAutoComplete = False
Worksheets("1").Activate
Worksheets("1").EnableSelection = xlUnlockedCells
Worksheets("1").Protect Contents:=True, UserInterfaceOnly:=True
Worksheets("1").Range("F5").Activate
If Worksheets.Count = 2 Then
Do
brojProstorija = InputBox("Koliko ima prostorija u objektu?", "Broj prostorija!")
If (Val(brojProstorija) > 100) Then
MsgBox ("Broj prostorija mora biti manji od 101!")
End If
If (brojProstorija = "") Then
MsgBox ("Niste unijeli broj!")
End If
If (Val(brojProstorija) = 0) And (brojProstorija <> "") Then
MsgBox ("Kako to mislite 0 prostorija?")
End If
Loop Until (brojProstorija <> "") And (Val(brojProstorija) < 100) And (Val(brojProstorija) <> 0)
Do
hk = InputBox("Kolika je vrijednost Hk za zgradu?", "Karakteristika zgrade!")
If (hk = "") Then
MsgBox ("Niste unijeli broj!")
End If
If (CDbl(hk) = 0) And (hk <> "") Then
MsgBox ("Kako to mislite 0?")
End If
Loop Until (hk <> "") And (CDbl(hk) <> 0)
zarez = InStr(1, hk, ",")
duzina = Len(hk)
If zarez <> 0 Then
lijeviDio = Left(hk, zarez - 1)
desniDio = Right(hk, duzina - zarez)
hk = lijeviDio + "." + desniDio
End If
Worksheets("1").Cells(22, "B").Value = Val(hk)
For i = 2 To Val(brojProstorija)
Sheets("1").Copy After:=Sheets(i - 1)
ActiveSheet.Name = i
ActiveSheet.Unprotect
ActiveSheet.Cells(5, "E").Value = Str(i)
Randomize
boja = Int((14 * Rnd) + 1)
ActiveSheet.Tab.ColorIndex = boja
ActiveSheet.Protect
Next i
Else
Sheets("1").Activate
Exit Sub
End If
Sheets("1").Activate
End Sub
When I open the root directory with Windows Explorer, i find that hidden file exactly beside the Excel file. Why can't he find it then????? The file is right there beside it!! And the file is visible, and not hidden. And immeditely after that he giives me another error message: Run-time error '1004': Copy method of Worksheet class failed. This is the line invoking this error: Sheets("1").Copy After:=Sheets(i - 1)
The macro works fine in the original file, but as soon i copy it or save it with the save as command, it looses it's link to some excel hidden file.
Here is the entire code:
Private Sub Workbook_Open()
Dim brojProstorija As String
Dim i As Integer
Dim hk As String
Dim zarez As Integer
Dim lijeviDio As String
Dim desniDio As String
Dim duzina As Integer
Dim boja As Integer
Application.WindowState = xlMaximized
Application.CellDragAndDrop = False
Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlToRight
Worksheets("1").PageSetup.BlackAndWhite = True
Worksheets("Rekapitulacija").PageSetup.BlackAndWhite = True
Application.EnableAutoComplete = False
Worksheets("1").Activate
Worksheets("1").EnableSelection = xlUnlockedCells
Worksheets("1").Protect Contents:=True, UserInterfaceOnly:=True
Worksheets("1").Range("F5").Activate
If Worksheets.Count = 2 Then
Do
brojProstorija = InputBox("Koliko ima prostorija u objektu?", "Broj prostorija!")
If (Val(brojProstorija) > 100) Then
MsgBox ("Broj prostorija mora biti manji od 101!")
End If
If (brojProstorija = "") Then
MsgBox ("Niste unijeli broj!")
End If
If (Val(brojProstorija) = 0) And (brojProstorija <> "") Then
MsgBox ("Kako to mislite 0 prostorija?")
End If
Loop Until (brojProstorija <> "") And (Val(brojProstorija) < 100) And (Val(brojProstorija) <> 0)
Do
hk = InputBox("Kolika je vrijednost Hk za zgradu?", "Karakteristika zgrade!")
If (hk = "") Then
MsgBox ("Niste unijeli broj!")
End If
If (CDbl(hk) = 0) And (hk <> "") Then
MsgBox ("Kako to mislite 0?")
End If
Loop Until (hk <> "") And (CDbl(hk) <> 0)
zarez = InStr(1, hk, ",")
duzina = Len(hk)
If zarez <> 0 Then
lijeviDio = Left(hk, zarez - 1)
desniDio = Right(hk, duzina - zarez)
hk = lijeviDio + "." + desniDio
End If
Worksheets("1").Cells(22, "B").Value = Val(hk)
For i = 2 To Val(brojProstorija)
Sheets("1").Copy After:=Sheets(i - 1)
ActiveSheet.Name = i
ActiveSheet.Unprotect
ActiveSheet.Cells(5, "E").Value = Str(i)
Randomize
boja = Int((14 * Rnd) + 1)
ActiveSheet.Tab.ColorIndex = boja
ActiveSheet.Protect
Next i
Else
Sheets("1").Activate
Exit Sub
End If
Sheets("1").Activate
End Sub