Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 & Vista OS
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:P").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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 & Vista OS
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" wrote: 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:P").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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 & Vista OS
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. -- Jeannell "Barb Reinhardt" wrote: 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" wrote: 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:P").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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 & Vista OS
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. "jsmith" wrote: 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. -- Jeannell "Barb Reinhardt" wrote: 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" wrote: 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:P").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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 & Vista OS
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. "jsmith" wrote: 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. -- Jeannell "Barb Reinhardt" wrote: 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" wrote: 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:P").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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 & Vista OS
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:P").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. "Barb Reinhardt" wrote: 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. "jsmith" wrote: 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. -- Jeannell "Barb Reinhardt" wrote: 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" wrote: 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:P").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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 & Vista OS
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 On Nov 5, 8:27*pm, jsmith wrote: 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:P").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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 & Vista OS
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. -- Jeannell " wrote: 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 On Nov 5, 8:27 pm, jsmith wrote: 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:P").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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 on Vista | Excel Worksheet Functions | |||
Excel 2007 and Office 2007 stink (so does Vista) | Excel Discussion (Misc queries) | |||
vista and excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 and Vista | Excel Discussion (Misc queries) | |||
Vista bug (?) regarding Excel 2007 | Excel Programming |