runtime error 9 in my macro

[Avassiliadi]

Hi all
I have made a macro in order to extract all the rows containing
dashboard.
but when I run it I have a problem at
Workbooks(strCurrentOrigFileExt).Activate (where I've written Problem
HERE) as a comment it says runtime error 9
anyone knows why?

tell me if you need more info
thanks
'
' Macro1 Macro
' Macro recorded 12/02/2008 by Alexandre Vassiliadi '


Dim strOrigDir
Dim strDestDir
Dim strOrigFile
Dim strOrigFileExt
Dim strDestFile
Dim strDestFileExt
Dim strCurrentOrigFile
Dim strCurrentOrigFileExt
Dim strOrigWorksheet
Dim strDestWorksheet
Dim intOrigLastRow
Dim intDestLastRow


Sub main()

strOrigDir = "U:\Training\Macro\Server Files"
strDestDir = "U:\Training\Macro\Server Files\Result EPM\"

ChDir "U:\Training\Macro\Server Files"

strOrigFile = "Server*"
strOrigFileExt = strOrigFile & ".log"

strDestFile = "EPM Server Logs"
strDestFileExt = strDestFile & ".csv"

Application.ScreenUpdating = False
Application.DisplayAlerts = False

OpenDestFile

'--- Search Files ---

With Application.FileSearch
.NewSearch
.LookIn = strOrigDir 'folder,where all new .log files are.
.SearchSubFolders = False
.Filename = strOrigFileExt

If .Execute() = 0 Then
' Not Found
End If

For i = 1 To .FoundFiles.Count - 1

Workbooks.OpenText Filename:=.FoundFiles(i), _
Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1),
Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True

intOrigLastRow =
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
intLenFoundFiles = Len(.FoundFiles(i))
intLenstrOrigDir = Len(strOrigDir)
strCurrentOrigFileExt = Mid(.FoundFiles(i),
intLenstrOrigDir + 1, intLenFoundFiles - intLenstrOrigDir)
strCurrentOrigFile = Mid(strCurrentOrigFileExt, 1,
Len(strCurrentOrigFileExt) - 4)

'--- PROBLEM HERE ---
Workbooks(strCurrentOrigFileExt).Activate

For intRow = 1 To intOrigLastRow
Workbooks(strCurrentOrigFileExt).Activate
Range("E" & intRow).Select
If ActiveCell.Value = "Dashboard" Then
Range("G" & intRow).Select
If Not IsEmpty(ActiveCell.Value) Then
Rows(intRow & ":" & intRow).Copy
WriteDestFile
End If
End If
Next

SaveAsOrigFile
CloseOrigFile
'delete .log

Next i

End With

SaveDestFile
CloseDestFile

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

' --- Open EPM Server Logs ---

Sub OpenDestFile()

Workbooks.Open (strDestDir & strDestFileExt)

End Sub

' --- Paste all data ---

Sub WriteDestFile()
'Sheets(strDestWorksheet).Select

Workbooks(strDestFile).Activate
intDestLastRow =
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Range("A" & intDestLastRow).Select
ActiveSheet.Paste

End Sub

' --- Save EPM Server Logs

Sub SaveDestFile()

Workbooks(strDestFileExt).Save

End Sub

' --- Close EPM Server Logs ---

Sub CloseDestFile()

Workbooks(strDestFileExt).Close

End Sub

' --- Save Server Log into Text file ( so the Log is not pasted twice
next time we run the macro ---

Sub SaveAsOrigFile()

ActiveWorkbook.SaveAs Filename:= _
strOrigDir & strCurrentOrigFile, FileFormat:= _
xlText, CreateBackup:=False

End Sub

' --- Close Current Workbook ---

Sub CloseOrigFile()

Workbooks(strCurrentOrigFile).Close

End Sub

 

[Jim Cone]

Looks like...
Workbooks(strCurrentOrigFileExt).Activate
Should be...
Workbooks(strCurrentOrigFile).Activate

Also, enter Option Explicit as the first line in your module and see what happens.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




<[email protected]>
wrote in message
Hi all
I have made a macro in order to extract all the rows containing
dashboard.
but when I run it I have a problem at
Workbooks(strCurrentOrigFileExt).Activate (where I've written Problem
HERE) as a comment it says runtime error 9
anyone knows why?

tell me if you need more info
thanks

-snip-

 

[Avassiliadi]

Thanks Jim,
That worked.