Plz help me to solve !!!!

[Amish]

Hi All !

I am new joinee to mrexcel forum.I have written one code and i am stuck at one line which has stop me for further designing of my code.

Code :-
Sub Main_report()
'
' Main_report Macro
' Macro recorded 2008-06-10 by amish
'
' Main

MsgBox ("Open Syslog Main Report")
Dim vaReport As Variant
Dim i As Integer
Dim wb As Object

vaReport = Application.GetOpenFilename _
(FileFilter:="Excel Filer (*.xls),*.xls", _
Title:="Select Standard Syslog Report", MultiSelect:=False)
Workbooks.Open vaReport
Workbooks.OpenText Filename:="Main.xls", _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:=":", FieldInfo:= _
Array(1, 1), TrailingMinusNumbers:=True
Cells.Find(What:="reset", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
"Want to activate open vaReport here coz it contain below mentioned Sheet "
Sheets("Syslog-summary-Bangalore").Select
Range("A1").Select
-----------------------------------------------------------------------
I am only pasting beginining part of my whole code.Now my problem is i am asking to open Main report using variant variable that is happening succesful now later in my code i have to activate this report.
I have try many ways but getting only error.
So plz show how to activate open Report !!!!!!

 

[mikerickson]

Use a WorkBook variable

Dim vaReportWorkBook as Workbook
   Rem code
Set vaReportWorkBook = Workbooks.Open vaReport 
   Rem code
vaReportWorkBook.Activate

 

[Amish]

Hi mikerickson !

I have done excatly as u said but it is giving me error " Expected end of Statement" with vaReport as marked

MsgBox ("Open Syslog Main Report")
Dim vaReport As Variant
Dim vaReportWorkBook As Workbook

Dim i As Integer
Dim wb As Object

vaReport = Application.GetOpenFilename _
(FileFilter:="Excel Filer (*.xls),*.xls", _
Title:="Select Standard Syslog Report", MultiSelect:=False)
Set vaReportWorkBook = Workbooks.Open vaReport



' Workbooks.Open vaReport

vaReportWorkBook.Activate

 

[mikerickson]

Set vaReportWorkBook = Workbooks.Open(vaReport)

 

[Amish]

Thanks a Ton !!!! mikerickson

It is working !!!!.
Can u give me some code for ----
Suppose my macro open some workbook and if it doesnt find any data in it, then it should not stuck with error but it should close that workbook and open another workbook .

Workbooks.OpenText Filename:="Main.xls", _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:=":", FieldInfo:= _
Array(1, 1), TrailingMinusNumbers:=True
Cells.Find(What:="reset", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
vaReportWorkBook.Activate
Sheets("Syslog-summary-Bangalore").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(1, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=ActiveCell.Range("A1:B1"), Type:= _
xlFillDefault
ActiveCell.Range("A1:B1").Select
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Copy
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("Main.xls").Activate
ActiveWindow.Close

' ***************************************************************************
' Dropping
Windows("Report_macro.xls").Activate
ActiveWindow.WindowState = xlNormal
Workbooks.OpenText Filename:= _
"dropping.xls", Origin:=437, StartRow:= _
1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, 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)), TrailingMinusNumbers:=True
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy


Like if in the beginining it opens main.xls and if it doesnt find any data in it , it should go directly to Dropping part code.

 

[Amish]

Hi !!
Any one plz give me some idea to overcome below problem.

Thanks a Ton !!!! mikerickson

It is working !!!!.
Can u give me some code for ----
Suppose my macro open some workbook and if it doesnt find any data in it, then it should not stuck with error but it should close that workbook and open another workbook .

Workbooks.OpenText Filename:="Main.xls", _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:=":", FieldInfo:= _
Array(1, 1), TrailingMinusNumbers:=True
Cells.Find(What:="reset", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
vaReportWorkBook.Activate
Sheets("Syslog-summary-Bangalore").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(1, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=ActiveCell.Range("A1:B1"), Type:= _
xlFillDefault
ActiveCell.Range("A1:B1").Select
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Copy
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Windows("Main.xls").Activate
ActiveWindow.Close

' ***************************************************************************
' Dropping
Windows("Report_macro.xls").Activate
ActiveWindow.WindowState = xlNormal
Workbooks.OpenText Filename:= _
"dropping.xls", Origin:=437, StartRow:= _
1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, 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)), TrailingMinusNumbers:=True
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy


Like if in the beginining it opens main.xls and if it doesnt find any data in it , it should go directly to Dropping part code.