Hi Guys,

I have read a lot of posts but I still can not make it work on Excel
2002.

My Code


Sub Get_Report_Data_Closed()

Dim FileName As Variant
Dim extractsize As Integer

FileName = Application.GetOpenFilename _
(filefilter:="Excel Wookbook(*.xls),*.txt,All Files
(*.*),*.*")
If FileName = False Then
MsgBox "You did not select a file."
Sheets("SetupSummary").Select
Exit Sub
End If

Application.ScreenUpdating = False

Sheets("ClosedTicketReport").Select
Range("A:Z").Select
Selection.ClearContents

Set xlapp = New Excel.Application
Set xlbook = xlapp.Workbooks.Open(FileName, False, True)

Set xlSheet = xlbook.Worksheets(1)
sheetcount = ThisWorkbook.Sheets.Count

ThisWorkbook.Sheets.Add After:=Worksheets(sheetcount)
xlSheet.UsedRange.Copy
ThisWorkbook.Sheets(sheetcount + 1).Paste
Selection.Copy
Sheets("ClosedTicketReport").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:Z").EntireColumn.AutoFit
Application.DisplayAlerts = False
Worksheets(sheetcount + 1).Delete
Application.DisplayAlerts = True

'The following code is problemetic

xlapp.ActiveWorkbook.Close
Set xlbook = Nothing
xlapp.Application.Quit
Set xlapp = Nothing

End Sub

Can someone please help me. This code works on my Excel 2003 at home
but not at work where I have Excel 2002.

Thanks a lot in advance.

Mo,

I was trying to make my way thru your code and got confused.
Why are you opening a new instance of Excel?
It appears you already have Excel open and if so, you can open additional
workbooks in the same instance.
Also, it appears as if you add a new sheet (sheetcount +1), paste in to it
and then delete it?
Could you provide a narrative of what you want to do?

Regards,
Jim Cone
San Francisco, USA


"Mo"
wrote...
Hi Guys,
I have read a lot of posts but I still can not make it work on Excel
2002.
My Code

Sub Get_Report_Data_Closed()

Dim FileName As Variant
Dim extractsize As Integer

FileName = Application.GetOpenFilename _
(filefilter:="Excel Wookbook(*.xls),*.txt,All Files(*.*),*.*")
If FileName = False Then
MsgBox "You did not select a file."
Sheets("SetupSummary").Select
Exit Sub
End If
Application.ScreenUpdating = False
Sheets("ClosedTicketReport").Select
Range("A:Z").Select
Selection.ClearContents
Set xlapp = New Excel.Application
Set xlbook = xlapp.Workbooks.Open(FileName, False, True)
Set xlSheet = xlbook.Worksheets(1)
sheetcount = ThisWorkbook.Sheets.Count
ThisWorkbook.Sheets.Add After:=Worksheets(sheetcount)
xlSheet.UsedRange.Copy
ThisWorkbook.Sheets(sheetcount + 1).Paste
Selection.Copy
Sheets("ClosedTicketReport").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:Z").EntireColumn.AutoFit
Application.DisplayAlerts = False
Worksheets(sheetcount + 1).Delete
Application.DisplayAlerts = True

'The following code is problemetic
xlapp.ActiveWorkbook.Close
Set xlbook = Nothing
xlapp.Application.Quit
Set xlapp = Nothing
End Sub

Can someone please help me. This code works on my Excel 2003 at home
but not at work where I have Excel 2002.
Thanks a lot in advance.

Hi Jim,
I understood that I don't need to create and delete the extra sheet.
Thanks for that and i will fix that part.

Background: I am getting some data from another file. So basically
copying the entire sheet from the other file into my opened file. I
tried opening the other file/workbook in the same excel instance
containing the macro, but the macro was not working as i didn't know
how to go back and forth between the two workbooks in the same excel
instance. therefore i created a new excel instance and opened the data
file there.

Further Information: At work we have an add-in that prompts u to press
"ok" when you open a new excel instance. I am assuming this is what
making the excel instance from closing. Am i correct. if so, how do i
get rid of this? your help is much appreciated.

Mo,

The following code copies the entire first sheet in the
FileName workbook into the workbook containing the code.

Regards,
Jim Cone
San Francisco, USA

'-------------------------------------
Sub Get_Report_Data_Closed()
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim CurrentWB As Excel.Workbook
Dim FileName As Variant

FileName = Application.GetOpenFilename _
(filefilter:="Excel Workbook(*.xls),*.txt,All Files(*.*),*.*")
If FileName = False Then
MsgBox "You did not select a file."
Sheets("SetupSummary").Select
Exit Sub
End If

Application.ScreenUpdating = False
Set CurrentWB = ThisWorkbook
Set xlBook = Workbooks.Open(FileName, False, True)
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Copy after:=CurrentWB.Sheets(CurrentWB.Sheets.Count)
xlBook.Close savechanges:=False
Application.ScreenUpdating = True

Set xlSheet = Nothing
Set xlBook = Nothing
Set CurrentWB = Nothing
End Sub
'--------------------------------------


"Mo" wrote in message oups.com...
Hi Jim,
I understood that I don't need to create and delete the extra sheet.
Thanks for that and i will fix that part.

Background: I am getting some data from another file. So basically
copying the entire sheet from the other file into my opened file. I
tried opening the other file/workbook in the same excel instance
containing the macro, but the macro was not working as i didn't know
how to go back and forth between the two workbooks in the same excel
instance. therefore i created a new excel instance and opened the data
file there.

Further Information: At work we have an add-in that prompts u to press
"ok" when you open a new excel instance. I am assuming this is what
making the excel instance from closing. Am i correct. if so, how do i
get rid of this? your help is much appreciated.

Thanks a lot Jim. Your work around took care of the unnecessary new
instance problem. It works now.