Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.htm error
I have the following code:
Option Explicit Sub Consolidate() Dim MyPath As String Dim FilesInPath As String Dim MyFiles() As String Dim Fnum As Long Dim mybook As Workbook Dim CalcMode As Long Dim sh As Worksheet Dim ErrorYes As Boolean Dim DCLastRow As Integer 'DirectorCopy Dim MCLastRow As Integer 'Monthly Compiler Dim CMonth As String 'Compile Month Dim CYear As String 'Compile Year Dim Month As Integer Dim MonthFilter As String Dim center(18) As String center(1) = "Bardstown" center(2) = "Bothell" center(3) = "VCollinsville" center(4) = "El Paso" center(5) = "Evansville" center(6) = "Greensboro" center(7) = "VHeathrow" center(8) = "Joplin" center(9) = "Kennesaw" center(10) = "Lafayette" center(11) = "Malvern" center(12) = "VManhattan" center(13) = "VMansfield" center(14) = "VOttawa" center(15) = "VPonco City" center(16) = "VReno" center(17) = "VSioux City" center(18) = "VTerra Haute" Dim FileCount As Long Dim ScoringAve As Double Dim i As Long Workbooks("Monthly PF Compiler").Activate ' If Cells(13, 4).Value = "January" Then Month = 1 ' If Cells(13, 4).Value = "February" Then Month = 2 ' If Cells(13, 4).Value = "March" Then Month = 3 ' If Cells(13, 4).Value = "April" Then Month = 4 ' If Cells(13, 4).Value = "May" Then Month = 5 ' If Cells(13, 4).Value = "June" Then Month = 6 ' If Cells(13, 4).Value = "July" Then Month = 7 ' If Cells(13, 4).Value = "August" Then Month = 8 ' If Cells(13, 4).Value = "September" Then Month = 9 ' If Cells(13, 4).Value = "October" Then Month = 10 ' If Cells(13, 4).Value = "November" Then Month = 11 ' If Cells(13, 4).Value = "December" Then Month = 12 ' CMonth = MonthName(Month, True) 'This one line of code replaces the above 13 lines CMonth = Left(Cells(13, 4).Value, 3) CYear = Right(Cells(13, 7).Value, 2) 'Fill in the path\folder where the files are MyPath = "X:\C&A Analysts Team\PF Process\1 Tally & PF's Work in Progress\Centers\" For i = 1 To 18 ' 'Add a slash at the end if the user forget it ' If Right(MyPath, 1) < "\" Then ' MyPath = MyPath & "\" ' End If 'If there are no Excel files in the folder exit the sub MonthFilter = MyPath & center(i) & "\*" & CMonth & " " & CYear & "*.xl*" FilesInPath = Dir(MonthFilter) If FilesInPath = "" Then MsgBox "No files found in " & center(i) GoTo ContinueLoop End If If FilesInPath < "" Then FileCount = FileCount + 1 End If 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" If InStr(1, FilesInPath, CMonth & " " & CYear, vbTextCompare) Then Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() End If Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & center(i) & "\" & MyFiles(Fnum)) On Error GoTo 0 If Not mybook Is Nothing Then 'Need to do the following: 'if lazy eye hasn't been run in directorcopy then run it With mybook.Worksheets("DirectorCopy") If .Cells(1, 1) = "" Then 'Application.Run "DirectorFormat" Application.Run "'Test Tally SheetIII'!DirectorFormat" This was running just fine earlier. My Excel locked up on me so I closed all the workbooks and re-opened them. Now I'm getting the following Run-Time error '1004': 'Test Tally SheetIII.htm" could not be found. Check the spelling of the file name, and verify that the file location is correct. If you are trying to open the file from you list of most recently used files, make sure that the file has not been renamed, moved, or deleted. Test Tally SheetIII is open. I don't understand. Does the order in which you open the Workbook matter? If so that seems... wrong. Why was this working fine before and now it's not? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.htm error
I figured this out. I replaced:
Application.Run "'Test Tally SheetIII'!DirectorFormat" with: Application.Run "'Test Tally SheetIII.xlsm'!DirectorFormat" and now it works as intended. Not sure why it was working just fine before without the .xlsm though. "Bishop" wrote: I have the following code: Option Explicit Sub Consolidate() Dim MyPath As String Dim FilesInPath As String Dim MyFiles() As String Dim Fnum As Long Dim mybook As Workbook Dim CalcMode As Long Dim sh As Worksheet Dim ErrorYes As Boolean Dim DCLastRow As Integer 'DirectorCopy Dim MCLastRow As Integer 'Monthly Compiler Dim CMonth As String 'Compile Month Dim CYear As String 'Compile Year Dim Month As Integer Dim MonthFilter As String Dim center(18) As String center(1) = "Bardstown" center(2) = "Bothell" center(3) = "VCollinsville" center(4) = "El Paso" center(5) = "Evansville" center(6) = "Greensboro" center(7) = "VHeathrow" center(8) = "Joplin" center(9) = "Kennesaw" center(10) = "Lafayette" center(11) = "Malvern" center(12) = "VManhattan" center(13) = "VMansfield" center(14) = "VOttawa" center(15) = "VPonco City" center(16) = "VReno" center(17) = "VSioux City" center(18) = "VTerra Haute" Dim FileCount As Long Dim ScoringAve As Double Dim i As Long Workbooks("Monthly PF Compiler").Activate ' If Cells(13, 4).Value = "January" Then Month = 1 ' If Cells(13, 4).Value = "February" Then Month = 2 ' If Cells(13, 4).Value = "March" Then Month = 3 ' If Cells(13, 4).Value = "April" Then Month = 4 ' If Cells(13, 4).Value = "May" Then Month = 5 ' If Cells(13, 4).Value = "June" Then Month = 6 ' If Cells(13, 4).Value = "July" Then Month = 7 ' If Cells(13, 4).Value = "August" Then Month = 8 ' If Cells(13, 4).Value = "September" Then Month = 9 ' If Cells(13, 4).Value = "October" Then Month = 10 ' If Cells(13, 4).Value = "November" Then Month = 11 ' If Cells(13, 4).Value = "December" Then Month = 12 ' CMonth = MonthName(Month, True) 'This one line of code replaces the above 13 lines CMonth = Left(Cells(13, 4).Value, 3) CYear = Right(Cells(13, 7).Value, 2) 'Fill in the path\folder where the files are MyPath = "X:\C&A Analysts Team\PF Process\1 Tally & PF's Work in Progress\Centers\" For i = 1 To 18 ' 'Add a slash at the end if the user forget it ' If Right(MyPath, 1) < "\" Then ' MyPath = MyPath & "\" ' End If 'If there are no Excel files in the folder exit the sub MonthFilter = MyPath & center(i) & "\*" & CMonth & " " & CYear & "*.xl*" FilesInPath = Dir(MonthFilter) If FilesInPath = "" Then MsgBox "No files found in " & center(i) GoTo ContinueLoop End If If FilesInPath < "" Then FileCount = FileCount + 1 End If 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" If InStr(1, FilesInPath, CMonth & " " & CYear, vbTextCompare) Then Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() End If Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & center(i) & "\" & MyFiles(Fnum)) On Error GoTo 0 If Not mybook Is Nothing Then 'Need to do the following: 'if lazy eye hasn't been run in directorcopy then run it With mybook.Worksheets("DirectorCopy") If .Cells(1, 1) = "" Then 'Application.Run "DirectorFormat" Application.Run "'Test Tally SheetIII'!DirectorFormat" This was running just fine earlier. My Excel locked up on me so I closed all the workbooks and re-opened them. Now I'm getting the following Run-Time error '1004': 'Test Tally SheetIII.htm" could not be found. Check the spelling of the file name, and verify that the file location is correct. If you are trying to open the file from you list of most recently used files, make sure that the file has not been renamed, moved, or deleted. Test Tally SheetIII is open. I don't understand. Does the order in which you open the Workbook matter? If so that seems... wrong. Why was this working fine before and now it's not? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Error handling error # 1004 Run-time error | Excel Programming | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming |