Hello all.
eight years ago I used an MS-DOS based application to handle data in a
small organization. This application is still in use today (handling
hundreds of records each day). When we wanted to make charts from the
data, I used MS-Excel to extract the data from a text file (which is
prepared by the application for that purpose) and create a chart
automatically with a VBA Macro.
Everything worked fine. Over the years We upgraded from Excel 95 to
Excel 97 and to Excel 2000 with the same VBA code. The organization
just upgraded to Excel XP (2002), but now the VBA code seems to cause
some problems. There is an error message when the macro reaches the
"Refresh BackgroundQuery:=False" line, and I don't know what's wrong.

To be more accurate, the application automatically starts Excel using
an MS-DOS command (somethink like '\[OfficeDir]\Excel.exe
MyXlsFile.xls'. When I tried loading the Excel file from Windows
Explorer (a simple double-click), there was no problem. Moreover, when
I tried the application at my home, it hanged the minute Excel was
loaded and came back to life the minute I closed Excel. However, I'm
working on Windows XP pro and the organization uses Windows 95 (no
typo: Windows 95) and Windows 98SE, so I can't tell if there is a
problem the the version of the application doesn't totaly fit to
Windows XP (you'll be surprised to hear that there is a version
comaptible to Windows XP).

Yes, I know. MS-DOS based applications should be in the waste basket.
The problem is that I don't work there any more (just doing some
maintenance work once in a while on the application), the application
is really complicated, the data is precious and since it's not based
on Windows - there are no problems with it and no crashes :-)
(seriously - it works 24/7 for 8 years now, only stopping 3 times:
upgrading the machine, a hard disk failure and replacing printers).

Here is the part of the code that reads the date:

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Text
Files;DefaultDir=C:\MyDir;DriverId=27;MaxBufferSiz e=512;PageTimeout=5;"
_
, Destination:=Range("A1:B100"))
.Sql = Array("SELECT MyFile.Var1, VALUES" & Chr(13) & "" &
Chr(10) & "FROM `C:\MyDir`\MyFile.txt T") ' where T stands
' for the ODBC driver refernce name
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = False
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With

Anyone has some suggestions? (besides my obvious suggestion: go back
to Excel 2000)

Thank you,
Doron

Open a new workbook and set up the query manually. Get that working, then
you can delete the querytable, turn on the macro recorder and do it again
(Manually). Compare that recorded code to the code you have and make
appropriate adjustments.


--
Regards,
Tom Ogilvy


"Doron Hadar" wrote in message
om...
Hello all.
eight years ago I used an MS-DOS based application to handle data in a
small organization. This application is still in use today (handling
hundreds of records each day). When we wanted to make charts from the
data, I used MS-Excel to extract the data from a text file (which is
prepared by the application for that purpose) and create a chart
automatically with a VBA Macro.
Everything worked fine. Over the years We upgraded from Excel 95 to
Excel 97 and to Excel 2000 with the same VBA code. The organization
just upgraded to Excel XP (2002), but now the VBA code seems to cause
some problems. There is an error message when the macro reaches the
"Refresh BackgroundQuery:=False" line, and I don't know what's wrong.

To be more accurate, the application automatically starts Excel using
an MS-DOS command (somethink like '\[OfficeDir]\Excel.exe
MyXlsFile.xls'. When I tried loading the Excel file from Windows
Explorer (a simple double-click), there was no problem. Moreover, when
I tried the application at my home, it hanged the minute Excel was
loaded and came back to life the minute I closed Excel. However, I'm
working on Windows XP pro and the organization uses Windows 95 (no
typo: Windows 95) and Windows 98SE, so I can't tell if there is a
problem the the version of the application doesn't totaly fit to
Windows XP (you'll be surprised to hear that there is a version
comaptible to Windows XP).

Yes, I know. MS-DOS based applications should be in the waste basket.
The problem is that I don't work there any more (just doing some
maintenance work once in a while on the application), the application
is really complicated, the data is precious and since it's not based
on Windows - there are no problems with it and no crashes :-)
(seriously - it works 24/7 for 8 years now, only stopping 3 times:
upgrading the machine, a hard disk failure and replacing printers).

Here is the part of the code that reads the date:

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Text
Files;DefaultDir=C:\MyDir;DriverId=27;MaxBufferSiz e=512;PageTimeout=5;"
_
, Destination:=Range("A1:B100"))
.Sql = Array("SELECT MyFile.Var1, VALUES" & Chr(13) & "" &
Chr(10) & "FROM `C:\MyDir`\MyFile.txt T") ' where T stands
' for the ODBC driver refernce name
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = False
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With

Anyone has some suggestions? (besides my obvious suggestion: go back
to Excel 2000)

Thank you,
Doron