OLE Automation Problem of VB in Windows XP

[sname]

I have a VB program that worked well in all Windows versions prior to XP. This program uses OLE Automation to open an Excel Spreadsheet (’97 Version) to read and write data. The code is as follows:

Dim Ex As Excel.Application ,Bk As Workbook, St As Excel.Sheets, FileStr as String

Set Ex = CreateObject("Excel.Application&quot
FileStr=”ABC”
FileName = App.Path & "\Date File\" & FileStr & ".xls"
FileCopy App.Path & "\Template\Date Template.xls", FileName
Set Bk = Ex.Workbooks.Open(FileName, 0, False)
Set St = Bk.Sheets
.....

This program creates a spreadsheet by copying a template, then open the spreadsheet. In Windows XP the program has an error at line Set Bk = Ex.Workbooks.Open(FileName, 0, False). The error massage is Method ‘Open’ of object ‘Workbooks’ failed. I tried other methods of Workbooks but all failed. It seems that XP does not support Workbooks methods, or at least does not support Workbooks methods in Excel ’97. If I upgrade Excel from 97 to 2000, will the program be working?

Any ideas would be appreciated.

 

[rvBasic]

Are you sure FileCopy is properly executed, i.e. that the target file exists after it?

You should also Dim the FileName variable.

_________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]

 

[sname]

Thanks rvBasic. FileCopy was executed. FileName was also dimed somewhere. As I said, the program worked well in other versions prior XP. In XP I can also see the copied file, but workbooks methods fail.

 

[k2w]

http://support.microsoft.com/?kbid=302460

This might help you, I've noticed that the paramaters you pass are different from the ones shown in the documentation.