Excel 2007 & Vista OS

[jsmith]

Hi,

I've created many macros in Excel 2003 and 2007. Now I have a new laptop
with Vista OS and find that some of my previous macros won't run.
Specifically, the macro can start to run but is not performing certain steps
which causes it to error out in subsequent steps. The macro works fine in an
XP environment but not in Vista environment. Any suggestions?

See below for first section of macro causing problems. It is erroring out at
"podate format" because it has not allowed the user to select the proper 5003
Procurement Report to open, hence it doesn't have a podate to format.

I'm one of the first people in my group with Vista and running macros, even
my IT dept is stumped.

Thx Jeannell


'Allow the user to select the 5003 Procurement Report to open
Dim fd As FileDialog
Dim filename1 As String
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Title = "Please choose the 5003 Procurement Report file"
'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem1 As Variant
'Use a With...End With block to reference the FileDialog object.
With fd

'Use the Show method to display the File Picker dialog box and
return the user's action.
'The user pressed the action button.
If .Show = -1 Then

'Step through each string in the FileDialogSelectedItems
collection.
For Each vrtSelectedItem1 In .SelectedItems

'vrtSelectedItem is a String that contains the path of each
selected item.
'You can use any file I/O functions that you want to work
with this path.
filename1 = vrtSelectedItem1

Next vrtSelectedItem1
'The user pressed Cancel.
Else
End If
End With
'Open the FP file the user selected and get the workbook name
'fd.Execute

Workbooks.OpenText Filename:= _
filename1 _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 2), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1),
Array(14, 1), Array(15 _
, 1)), TrailingMinusNumbers:=True
Dim largebuyname As String
largebuyname = ActiveWorkbook.Name

'Delete the first five rows and change the header row text to wrap and
bold
' Rows("1:5").Select
' Selection.Delete Shift:=xlUp

' Added the next two lines of code to accomodate deletion of two new
columns in APS PRocurement report
' Columns("O").Select
' Selection.Delete Shift:=xlToLeft


Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Font.FontStyle = "Bold"
End With
'Sort by po_release_date
Columns("I:I").Select
Range("A:N").Sort Key1:=Range("I2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'Delete all rows with a po_release_time greater than Friday of the
current week
Dim cell As Object
Dim podate As Date
Dim cntr As Integer
cntr = 1
podate = Format(Now, "mm/dd/yyyy 23:59")
'MsgBox "First Date: " & podate
Do Until Weekday(podate) = vbFriday
podate = DateAdd("d", 1, podate)
Loop

 

[Barb Reinhardt]

A quick review of the PODate piece has it refer to NOW. There is nothing
there that I see that would cause a problem. Have you tried stepping through
the code line by line to find your problem?
--
HTH,
Barb Reinhardt

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

 

[jsmith]

Hi Barb,
Yes, I've tried stepping through but that is one of the problems, it does
not stop at the point where it is supposed to ask the user to open a file.
This is why it errors out, because it gets to a future step and wants to
format the file the user was supposed to open - only the macro didn't give
the user the opportunity.

When stepping through, it goes directly from starting the macro to error
without actually taking the steps defined in the code.

 

[Barb Reinhardt]

It looks like it stepped through it from here. I see you're trying to open a
file. What type of file are you trying to open and what do you want to do
with it?
--
HTH,
Barb Reinhardt

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

 

[Barb Reinhardt]

FWIW, I'm not on VISTA on this computer so can't test that. Where
SPECIFICALLY is it having an error.
--
HTH,
Barb Reinhardt

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

 

[Barb Reinhardt]

I did some code tweaks, but I'm wondering if it's acting on the wrong
workbook. You don't define which worksheet it's acting on anywhere.

Option Explicit

Sub Test()

'Allow the user to select the 5003 Procurement Report to open
Dim filename1 As String
Dim sFile As String
Dim ShortName As String
Dim myWB As Workbook
Dim AutoSec As MsoAutomationSecurity
Dim myWS As Worksheet

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Text files", "*.txt"
.FilterIndex = 1
.Title = "Please choose the 5003 Procurement Report file"
If .Show = False Then
MsgBox ("You cancelled opening the file.")
End
End If
sFile = .SelectedItems(1)
End With

ShortName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))

'Check if user cancelled to open macro if yes then end

Set myWB = Nothing

On Error Resume Next
Set myWB = Workbooks(ShortName)
On Error GoTo 0

'Opens Workbook if it's not already open
If myWB Is Nothing Then
AutoSec = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityLow
Workbooks.OpenText Filename:=sFile, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 2), Array(6, 1), _
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13,
1), _
Array(14, 1), Array(15 _
, 1)), TrailingMinusNumbers:=True
Set myWB = ActiveWorkbook

Application.AutomationSecurity = AutoSec
End If

'Open the FP file the user selected and get the workbook name
'fd.Execute


Dim largebuyname As String
largebuyname = myWB.Name

'Delete the first five rows and change the header row text to wrap and
'Bold
' Rows("1:5").Select
' Selection.Delete Shift:=xlUp

' Added the next two lines of code to accomodate deletion of two new
' columns in APS PRocurement report
' Columns("O").Select
' Selection.Delete Shift:=xlToLeft

Set myWS = ActiveSheet '<~~~Do you only have one worksheet
'in the open workbook

With myWS.Rows("1:1")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Font.FontStyle = "Bold"
End With
'Sort by po_release_date

myWS.Columns("I:I").Select
myWS.Range("A:N").Sort Key1:=Range("I2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'Delete all rows with a po_release_time greater than Friday of the
'current week
Dim cell As Object
Dim podate As Date
Dim cntr As Integer
cntr = 1
podate = Format(Now, "mm/dd/yyyy 23:59")
'MsgBox "First Date: " & podate
Do Until Weekday(podate) = vbFriday
podate = DateAdd("d", 1, podate)
Loop
End Sub

--
HTH,
Barb Reinhardt

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

 

[paul.robinson]

Hi
I've found that a scattering of

DoEvents

commands can help when using Vista. It just forces the OS to complete
the code run up to that point (and not go running off to do some other
damn thing you know nothing about...). It is also useful to speed
things up in vista if you are automating charts or other graphic
objects.
regards
Paul

 

[jsmith]

Hi All,
My problem has been resolved.
http://support.microsoft.com/kb/q208218/

I had to unchecked a missing reference in VB under Tools - References.
Thanks for the replies.
Now I just need to figure out why Exceell 2007 + Vista doesn't understand
the concept of stepping through a macro. When I step in to and select F8 the
first time then the entire macro runs without stopping.