RunTimeError-438 XL2007-->XL2003 Compatibility Issues

[Telecorder]

Can anyone help here?

Have a program coded in XL-2007 that runs great with no issues what-so-ever
(Thanks to a number of this forum contributors!). Since I'll have other users
that only have XL-2003 or earlier, I saved the files in 97-2003
compatibility. Running as 97-2003 in my XL-2007, the program runs great - as
I want it to w/no hiccups.

Files only show some instances of "Minor loss of fidelity" when checked. Yet
when run by others on XL-2003, had issues that causes a Run Time Error of 438
(Object doesn't support this property or method) w/Debug referencing the code
for indicated line-->

Private Sub Sampling_Click()
--> Workbooks.Open ("C:\My Documents\Workfiles\REQUEST WORKSHEET
97-2003.xls")
PromptDialog.Hide
End Sub

[Confirmed that the file path is spot on...]

Yet - -
Compatibility Report for Master.xls
Run on 2/2/2009 20:41
The following features in this workbook are not supported by earlier
versions of Excel. These features may be lost or degraded when you save this
workbook in an earlier file format.
Minor loss of fidelity# of occurrences 3

Some cells or styles in this workbook contain formatting that is not
supported by the selected file format. These formats will be converted to the
closest format available.

Is the Workbooks.open code not compatible in 97-2003 versions (or perhaps
the PromptDialog.hide)?



Rest of separate called reference files with filtered/consolidated data
shows 14 minor Loss of Fidelity issues -->
Yet, when run on xL2003, they return a Run Time Error 438 that DeBug shows
as issues --->

With Sheets("Summary").ListObjects(1) 'Delete old data
----> .AutoFilter.ShowAllData
m = .ListColumns.Count
ReDim sc(m + 1)
For i = 1 To m
sc(i) = .ListColumns(i).Name
Application.StatusBar = "Part Two " & Format$(i / m, "#00%") '<<<<
Next i


Is AutoFilter.showalldata an issue with older versions of XL?

And also at--->
Sub CombineTs()
Dim rr As Integer 'row count of Summary
Dim n As Integer 'sheet count
Dim m As Integer 'column count Summary
Dim t As Integer
Dim s() As Variant 'row count others
Dim u As Integer 'cumulative row count
Dim ss As String 'name of Summary
Dim sc() As Variant 'column names in Summary
Dim ssc() As Variant 'column count others
Dim i As Integer
Dim j As Integer

ss = "Summary"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

n = ActiveWorkbook.Worksheets.Count
ReDim s(n + 1)
ReDim ssc(n + 1)
u = 1
For t = 1 To n
----->If Not ss = Worksheets(t).Name And Worksheets(t).ListObjects.Count = 1
Then
s(t) = Worksheets(t).ListObjects(1).ListRows.Count
ssc(t) = Worksheets(t).ListObjects(1).ListColumns.Count
End If
Application.StatusBar = "Part One of Four " & Format$(t / n, "#00%") '<<<<
Next t


Not sure of what the issues are here...


Anyone with insights?

.... or do I need to code in native XL-2003 to ensure compatibility (Or
convince all to upgrade to XL-2007)?

 

[Barb Reinhardt]

I think I'd try something like this instead

Option Explicit

Private Sub Sampling_Click()
Dim myString As String
Dim myWB As Excel.Workbook

myString = "C:\My Documents\Workfiles\REQUEST WORKSHEET 97-2003.xls"
Set myWB = Nothing
On Error Resume Next
Set myWB = Workbooks.Open(myString)
On Error GoTo 0
If myWB Is Nothing Then
MsgBox ("Error in opening workbook.")
End
End If

'PromptDialog.Hide '<~~Got an error on this in Excel 2003 upon compile
End Sub


--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

Can anyone help here?

Have a program coded in XL-2007 that runs great with no issues what-so-ever
(Thanks to a number of this forum contributors!). Since I'll have other users
that only have XL-2003 or earlier, I saved the files in 97-2003
compatibility. Running as 97-2003 in my XL-2007, the program runs great - as
I want it to w/no hiccups.

Files only show some instances of "Minor loss of fidelity" when checked. Yet
when run by others on XL-2003, had issues that causes a Run Time Error of 438
(Object doesn't support this property or method) w/Debug referencing the code
for indicated line-->

Private Sub Sampling_Click()
--> Workbooks.Open ("C:\My Documents\Workfiles\REQUEST WORKSHEET
97-2003.xls")
PromptDialog.Hide
End Sub

[Confirmed that the file path is spot on...]

Yet - -
Compatibility Report for Master.xls
Run on 2/2/2009 20:41
The following features in this workbook are not supported by earlier
versions of Excel. These features may be lost or degraded when you save this
workbook in an earlier file format.
Minor loss of fidelity# of occurrences 3

Some cells or styles in this workbook contain formatting that is not
supported by the selected file format. These formats will be converted to the
closest format available.

Is the Workbooks.open code not compatible in 97-2003 versions (or perhaps
the PromptDialog.hide)?



Rest of separate called reference files with filtered/consolidated data
shows 14 minor Loss of Fidelity issues -->
Yet, when run on xL2003, they return a Run Time Error 438 that DeBug shows
as issues --->

With Sheets("Summary").ListObjects(1) 'Delete old data
----> .AutoFilter.ShowAllData
m = .ListColumns.Count
ReDim sc(m + 1)
For i = 1 To m
sc(i) = .ListColumns(i).Name
Application.StatusBar = "Part Two " & Format$(i / m, "#00%") '<<<<
Next i


Is AutoFilter.showalldata an issue with older versions of XL?

And also at--->
Sub CombineTs()
Dim rr As Integer 'row count of Summary
Dim n As Integer 'sheet count
Dim m As Integer 'column count Summary
Dim t As Integer
Dim s() As Variant 'row count others
Dim u As Integer 'cumulative row count
Dim ss As String 'name of Summary
Dim sc() As Variant 'column names in Summary
Dim ssc() As Variant 'column count others
Dim i As Integer
Dim j As Integer

ss = "Summary"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

n = ActiveWorkbook.Worksheets.Count
ReDim s(n + 1)
ReDim ssc(n + 1)
u = 1
For t = 1 To n
----->If Not ss = Worksheets(t).Name And Worksheets(t).ListObjects.Count = 1
Then
s(t) = Worksheets(t).ListObjects(1).ListRows.Count
ssc(t) = Worksheets(t).ListObjects(1).ListColumns.Count
End If
Application.StatusBar = "Part One of Four " & Format$(t / n, "#00%") '<<<<
Next t


Not sure of what the issues are here...


Anyone with insights?

... or do I need to code in native XL-2003 to ensure compatibility (Or
convince all to upgrade to XL-2007)?

 

[Barb Reinhardt]

I believe I've seen an error with ShowAllData if all data is already shown.
You may need to put some error checking on that one.

As far as getting folks to move to 2007, good luck. Let me know how that
works.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

Can anyone help here?

Have a program coded in XL-2007 that runs great with no issues what-so-ever
(Thanks to a number of this forum contributors!). Since I'll have other users
that only have XL-2003 or earlier, I saved the files in 97-2003
compatibility. Running as 97-2003 in my XL-2007, the program runs great - as
I want it to w/no hiccups.

Files only show some instances of "Minor loss of fidelity" when checked. Yet
when run by others on XL-2003, had issues that causes a Run Time Error of 438
(Object doesn't support this property or method) w/Debug referencing the code
for indicated line-->

Private Sub Sampling_Click()
--> Workbooks.Open ("C:\My Documents\Workfiles\REQUEST WORKSHEET
97-2003.xls")
PromptDialog.Hide
End Sub

[Confirmed that the file path is spot on...]

Yet - -
Compatibility Report for Master.xls
Run on 2/2/2009 20:41
The following features in this workbook are not supported by earlier
versions of Excel. These features may be lost or degraded when you save this
workbook in an earlier file format.
Minor loss of fidelity# of occurrences 3

Some cells or styles in this workbook contain formatting that is not
supported by the selected file format. These formats will be converted to the
closest format available.

Is the Workbooks.open code not compatible in 97-2003 versions (or perhaps
the PromptDialog.hide)?



Rest of separate called reference files with filtered/consolidated data
shows 14 minor Loss of Fidelity issues -->
Yet, when run on xL2003, they return a Run Time Error 438 that DeBug shows
as issues --->

With Sheets("Summary").ListObjects(1) 'Delete old data
----> .AutoFilter.ShowAllData
m = .ListColumns.Count
ReDim sc(m + 1)
For i = 1 To m
sc(i) = .ListColumns(i).Name
Application.StatusBar = "Part Two " & Format$(i / m, "#00%") '<<<<
Next i


Is AutoFilter.showalldata an issue with older versions of XL?

And also at--->
Sub CombineTs()
Dim rr As Integer 'row count of Summary
Dim n As Integer 'sheet count
Dim m As Integer 'column count Summary
Dim t As Integer
Dim s() As Variant 'row count others
Dim u As Integer 'cumulative row count
Dim ss As String 'name of Summary
Dim sc() As Variant 'column names in Summary
Dim ssc() As Variant 'column count others
Dim i As Integer
Dim j As Integer

ss = "Summary"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

n = ActiveWorkbook.Worksheets.Count
ReDim s(n + 1)
ReDim ssc(n + 1)
u = 1
For t = 1 To n
----->If Not ss = Worksheets(t).Name And Worksheets(t).ListObjects.Count = 1
Then
s(t) = Worksheets(t).ListObjects(1).ListRows.Count
ssc(t) = Worksheets(t).ListObjects(1).ListColumns.Count
End If
Application.StatusBar = "Part One of Four " & Format$(t / n, "#00%") '<<<<
Next t


Not sure of what the issues are here...


Anyone with insights?

... or do I need to code in native XL-2003 to ensure compatibility (Or
convince all to upgrade to XL-2007)?

 

[Telecorder]

Barb--
Many thanks for the reply and attempt. I tried the error trapping as
suggested but it returns the message box 'can't find' so it won't work on my
XL-2007. I guess I can send the re-coded file on and see if it works on their
XL-2003.

Problem is, I have users with 97/2000/2003 versions with me running XL-2007.
I wound up going to XL-2007 due to new PC running Vista. [Interestingly
enough, when I'm in Explorer and click on a file that has code, it locks
Explorer up just by it loading into the preview frame... ;-{ Have to use Task
Manager to reboot Explorer to get out of it...]

I guess my next attempt is to see if Vista will let me load an older XL
version and write the code in it without too many hiccups...

;-{

I think I'd try something like this instead

Option Explicit

Private Sub Sampling_Click()
Dim myString As String
Dim myWB As Excel.Workbook

myString = "C:\My Documents\Workfiles\REQUEST WORKSHEET 97-2003.xls"
Set myWB = Nothing
On Error Resume Next
Set myWB = Workbooks.Open(myString)
On Error GoTo 0
If myWB Is Nothing Then
MsgBox ("Error in opening workbook.")
End
End If

'PromptDialog.Hide '<~~Got an error on this in Excel 2003 upon compile
End Sub


--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

Can anyone help here?

Have a program coded in XL-2007 that runs great with no issues what-so-ever
(Thanks to a number of this forum contributors!). Since I'll have other users
that only have XL-2003 or earlier, I saved the files in 97-2003
compatibility. Running as 97-2003 in my XL-2007, the program runs great - as
I want it to w/no hiccups.

Files only show some instances of "Minor loss of fidelity" when checked. Yet
when run by others on XL-2003, had issues that causes a Run Time Error of 438
(Object doesn't support this property or method) w/Debug referencing the code
for indicated line-->

Private Sub Sampling_Click()
--> Workbooks.Open ("C:\My Documents\Workfiles\REQUEST WORKSHEET
97-2003.xls")
PromptDialog.Hide
End Sub

[Confirmed that the file path is spot on...]

Yet - -
Compatibility Report for Master.xls
Run on 2/2/2009 20:41
The following features in this workbook are not supported by earlier
versions of Excel. These features may be lost or degraded when you save this
workbook in an earlier file format.
Minor loss of fidelity# of occurrences 3

Some cells or styles in this workbook contain formatting that is not
supported by the selected file format. These formats will be converted to the
closest format available.

Is the Workbooks.open code not compatible in 97-2003 versions (or perhaps
the PromptDialog.hide)?



Rest of separate called reference files with filtered/consolidated data
shows 14 minor Loss of Fidelity issues -->
Yet, when run on xL2003, they return a Run Time Error 438 that DeBug shows
as issues --->

With Sheets("Summary").ListObjects(1) 'Delete old data
----> .AutoFilter.ShowAllData
m = .ListColumns.Count
ReDim sc(m + 1)
For i = 1 To m
sc(i) = .ListColumns(i).Name
Application.StatusBar = "Part Two " & Format$(i / m, "#00%") '<<<<
Next i


Is AutoFilter.showalldata an issue with older versions of XL?

And also at--->
Sub CombineTs()
Dim rr As Integer 'row count of Summary
Dim n As Integer 'sheet count
Dim m As Integer 'column count Summary
Dim t As Integer
Dim s() As Variant 'row count others
Dim u As Integer 'cumulative row count
Dim ss As String 'name of Summary
Dim sc() As Variant 'column names in Summary
Dim ssc() As Variant 'column count others
Dim i As Integer
Dim j As Integer

ss = "Summary"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

n = ActiveWorkbook.Worksheets.Count
ReDim s(n + 1)
ReDim ssc(n + 1)
u = 1
For t = 1 To n
----->If Not ss = Worksheets(t).Name And Worksheets(t).ListObjects.Count = 1
Then
s(t) = Worksheets(t).ListObjects(1).ListRows.Count
ssc(t) = Worksheets(t).ListObjects(1).ListColumns.Count
End If
Application.StatusBar = "Part One of Four " & Format$(t / n, "#00%") '<<<<
Next t


Not sure of what the issues are here...


Anyone with insights?

... or do I need to code in native XL-2003 to ensure compatibility (Or
convince all to upgrade to XL-2007)?

 

[Telecorder]

Well, I guess it's nice to work for a firm that realizes sometimes it's
better to move ahead... Our IT is sending Office Standard 2007 to those that
need it for compatibility. I guess I'll have to warn them to disable the
preview function in Vista for the folder as it locks up the Exporer window
with the Macros...

As far as getting folks to move to 2007, good luck. Let me know how that
works.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

Can anyone help here?

Have a program coded in XL-2007 that runs great with no issues what-so-ever
(Thanks to a number of this forum contributors!). Since I'll have other users
that only have XL-2003 or earlier, I saved the files in 97-2003
compatibility. Running as 97-2003 in my XL-2007, the program runs great - as
I want it to w/no hiccups.

Files only show some instances of "Minor loss of fidelity" when checked. Yet
when run by others on XL-2003, had issues that causes a Run Time Error of 438
(Object doesn't support this property or method) w/Debug referencing the code
for indicated line-->

Private Sub Sampling_Click()
--> Workbooks.Open ("C:\My Documents\Workfiles\REQUEST WORKSHEET
97-2003.xls")
PromptDialog.Hide
End Sub

[Confirmed that the file path is spot on...]

Yet - -
Compatibility Report for Master.xls
Run on 2/2/2009 20:41
The following features in this workbook are not supported by earlier
versions of Excel. These features may be lost or degraded when you save this
workbook in an earlier file format.
Minor loss of fidelity# of occurrences 3

Some cells or styles in this workbook contain formatting that is not
supported by the selected file format. These formats will be converted to the
closest format available.

Is the Workbooks.open code not compatible in 97-2003 versions (or perhaps
the PromptDialog.hide)?



Rest of separate called reference files with filtered/consolidated data
shows 14 minor Loss of Fidelity issues -->
Yet, when run on xL2003, they return a Run Time Error 438 that DeBug shows
as issues --->

With Sheets("Summary").ListObjects(1) 'Delete old data
----> .AutoFilter.ShowAllData
m = .ListColumns.Count
ReDim sc(m + 1)
For i = 1 To m
sc(i) = .ListColumns(i).Name
Application.StatusBar = "Part Two " & Format$(i / m, "#00%") '<<<<
Next i


Is AutoFilter.showalldata an issue with older versions of XL?

And also at--->
Sub CombineTs()
Dim rr As Integer 'row count of Summary
Dim n As Integer 'sheet count
Dim m As Integer 'column count Summary
Dim t As Integer
Dim s() As Variant 'row count others
Dim u As Integer 'cumulative row count
Dim ss As String 'name of Summary
Dim sc() As Variant 'column names in Summary
Dim ssc() As Variant 'column count others
Dim i As Integer
Dim j As Integer

ss = "Summary"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

n = ActiveWorkbook.Worksheets.Count
ReDim s(n + 1)
ReDim ssc(n + 1)
u = 1
For t = 1 To n
----->If Not ss = Worksheets(t).Name And Worksheets(t).ListObjects.Count = 1
Then
s(t) = Worksheets(t).ListObjects(1).ListRows.Count
ssc(t) = Worksheets(t).ListObjects(1).ListColumns.Count
End If
Application.StatusBar = "Part One of Four " & Format$(t / n, "#00%") '<<<<
Next t


Not sure of what the issues are here...


Anyone with insights?

... or do I need to code in native XL-2003 to ensure compatibility (Or
convince all to upgrade to XL-2007)?