| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro code error; machine dependent
I have created an excel spreadsheet for multiple users which enables them to
record data monthly. I created a macro which then produces another excel file containing a report of this data, so that the user does not have to send me their entire spreadsheet, which is an enormous file. Being a beginner at VBA, I recorded this macro and tested it thoroughly, and it worked fine. Now that the spreadsheet is being used by over 200 users, the macro is producing an error for some users and works fine for other users. I have again tested the macro in our office, and it works on some machines and not for others. The error that is produced is "Runtime error 1004: Unable to set the Paper Size property of the Page Setup class." This spreadsheet was created in Excel 2000. All machines tested have Excel 2000 and Windows XP. So, is there an option or setting in Excel that is contriubting to this error? I have users working with this spreadsheet already in multiple US states, so I am hoping to find a solution that would be easy for them to fix without redistributing the entire file. These users would also not be candidates for fixing the code themselves. I have included the code below. Thanks so much for any insight. Sub January2006() ' ' January2006 Macro ' Macro recorded 3/23/2005 ' ' ActiveSheet.Range("A1:L13").Select Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=False ActiveCell.Offset(7, 0).Rows("1:1").EntireRow.RowHeight = 69 Selection.Interior.ColorIndex = xlNone ActiveWindow.DisplayGridlines = False ActiveWorkbook.DisplayDrawingObjects = xlHide ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 15.57 ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 21 With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 End With Dim strPath As String Dim strFileName As String Range("C2:F2").Select strPath = Application.DefaultFilePath & "\" strFileName = Range("C2").Value & "_January 2006 Report" ActiveWorkbook.SaveAs strPath & strFileName, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ActiveWindow.Close Sheets("Intro--Start Here").Select MsgBox ("The January 2006 file has been saved to your My Documents folder. Please email file to XXX") End Sub |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro code error; machine dependent
SJC,
Have you checked out if the machines throwing the error have printers that can handle letter size paper? Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "SJC" wrote in message ... I have created an excel spreadsheet for multiple users which enables them to record data monthly. I created a macro which then produces another excel file containing a report of this data, so that the user does not have to send me their entire spreadsheet, which is an enormous file. Being a beginner at VBA, I recorded this macro and tested it thoroughly, and it worked fine. Now that the spreadsheet is being used by over 200 users, the macro is producing an error for some users and works fine for other users. I have again tested the macro in our office, and it works on some machines and not for others. The error that is produced is "Runtime error 1004: Unable to set the Paper Size property of the Page Setup class." This spreadsheet was created in Excel 2000. All machines tested have Excel 2000 and Windows XP. So, is there an option or setting in Excel that is contriubting to this error? I have users working with this spreadsheet already in multiple US states, so I am hoping to find a solution that would be easy for them to fix without redistributing the entire file. These users would also not be candidates for fixing the code themselves. I have included the code below. Thanks so much for any insight. Sub January2006() ' ' January2006 Macro ' Macro recorded 3/23/2005 ' ' ActiveSheet.Range("A1:L13").Select Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=False ActiveCell.Offset(7, 0).Rows("1:1").EntireRow.RowHeight = 69 Selection.Interior.ColorIndex = xlNone ActiveWindow.DisplayGridlines = False ActiveWorkbook.DisplayDrawingObjects = xlHide ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 15.57 ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 21 With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 End With Dim strPath As String Dim strFileName As String Range("C2:F2").Select strPath = Application.DefaultFilePath & "\" strFileName = Range("C2").Value & "_January 2006 Report" ActiveWorkbook.SaveAs strPath & strFileName, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ActiveWindow.Close Sheets("Intro--Start Here").Select MsgBox ("The January 2006 file has been saved to your My Documents folder. Please email file to XXX") End Sub |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro code error; machine dependent
Yes, all of the machines in our office have the same printer drivers and
printers, yet some produce the error and some do not. "Jim Cone" wrote: SJC, Have you checked out if the machines throwing the error have printers that can handle letter size paper? Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "SJC" wrote in message ... I have created an excel spreadsheet for multiple users which enables them to record data monthly. I created a macro which then produces another excel file containing a report of this data, so that the user does not have to send me their entire spreadsheet, which is an enormous file. Being a beginner at VBA, I recorded this macro and tested it thoroughly, and it worked fine. Now that the spreadsheet is being used by over 200 users, the macro is producing an error for some users and works fine for other users. I have again tested the macro in our office, and it works on some machines and not for others. The error that is produced is "Runtime error 1004: Unable to set the Paper Size property of the Page Setup class." This spreadsheet was created in Excel 2000. All machines tested have Excel 2000 and Windows XP. So, is there an option or setting in Excel that is contriubting to this error? I have users working with this spreadsheet already in multiple US states, so I am hoping to find a solution that would be easy for them to fix without redistributing the entire file. These users would also not be candidates for fixing the code themselves. I have included the code below. Thanks so much for any insight. Sub January2006() ' ' January2006 Macro ' Macro recorded 3/23/2005 ' ' ActiveSheet.Range("A1:L13").Select Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=False ActiveCell.Offset(7, 0).Rows("1:1").EntireRow.RowHeight = 69 Selection.Interior.ColorIndex = xlNone ActiveWindow.DisplayGridlines = False ActiveWorkbook.DisplayDrawingObjects = xlHide ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 15.57 ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 21 With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 End With Dim strPath As String Dim strFileName As String Range("C2:F2").Select strPath = Application.DefaultFilePath & "\" strFileName = Range("C2").Value & "_January 2006 Report" ActiveWorkbook.SaveAs strPath & strFileName, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ActiveWindow.Close Sheets("Intro--Start Here").Select MsgBox ("The January 2006 file has been saved to your My Documents folder. Please email file to XXX") End Sub |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro code error; machine dependent
SJC,
Well you got me. However I did condense the code a little and commented out the paper size code line. It might be worth a try it to see if it works on one of the problem machines. Jim Cone San Francisco, USA '----------------------------- Sub January2006() ' January2006 Macro ' Macro recorded 3/23/2005 ActiveSheet.Range("A1:L13").Select Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlAll ActiveCell.Offset(7, 0).EntireRow.RowHeight = 69 Selection.Interior.ColorIndex = xlNone ActiveWindow.DisplayGridlines = False ActiveWorkbook.DisplayDrawingObjects = xlHide ActiveCell.Offset(0, 1).EntireColumn.ColumnWidth = 21 With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" .PrintArea = "" .LeftMargin = 54 .RightMargin = 54 .TopMargin = 72 .BottomMargin = 72 .HeaderMargin = 36 .FooterMargin = 36 .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False ' .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 End With Dim strPath As String Dim strFileName As String Range("C2:F2").Select strPath = Application.DefaultFilePath & "\" strFileName = Range("C2").Value & "_January 2006 Report" ActiveWorkbook.SaveAs strPath & strFileName ActiveWindow.Close Sheets("Intro--Start Here").Select MsgBox ("The January 2006 file has been saved to your My Documents folder. " _ & vbCr & "Please email file to XXX") End Sub '------------------ "SJC" wrote in message Yes, all of the machines in our office have the same printer drivers and printers, yet some produce the error and some do not. "Jim Cone" wrote: SJC, Have you checked out if the machines throwing the error have printers that can handle letter size paper? Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "SJC" wrote in message ... I have created an excel spreadsheet for multiple users which enables them to record data monthly. I created a macro which then produces another excel file containing a report of this data, so that the user does not have to send me their entire spreadsheet, which is an enormous file. Being a beginner at VBA, I recorded this macro and tested it thoroughly, and it worked fine. Now that the spreadsheet is being used by over 200 users, the macro is producing an error for some users and works fine for other users. I have again tested the macro in our office, and it works on some machines and not for others. The error that is produced is "Runtime error 1004: Unable to set the Paper Size property of the Page Setup class." This spreadsheet was created in Excel 2000. All machines tested have Excel 2000 and Windows XP. So, is there an option or setting in Excel that is contriubting to this error? I have users working with this spreadsheet already in multiple US states, so I am hoping to find a solution that would be easy for them to fix without redistributing the entire file. These users would also not be candidates for fixing the code themselves. I have included the code below. Thanks so much for any insight. Sub January2006() ' ' January2006 Macro ' Macro recorded 3/23/2005 ' ActiveSheet.Range("A1:L13").Select Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=False ActiveCell.Offset(7, 0).Rows("1:1").EntireRow.RowHeight = 69 Selection.Interior.ColorIndex = xlNone ActiveWindow.DisplayGridlines = False ActiveWorkbook.DisplayDrawingObjects = xlHide ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 15.57 ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 21 With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 End With Dim strPath As String Dim strFileName As String Range("C2:F2").Select strPath = Application.DefaultFilePath & "\" strFileName = Range("C2").Value & "_January 2006 Report" ActiveWorkbook.SaveAs strPath & strFileName, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ActiveWindow.Close Sheets("Intro--Start Here").Select MsgBox ("The January 2006 file has been saved to your My Documents folder. Please email file to XXX") End Sub |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro code error; machine dependent
Hi Jim, thanks for all of your help. Yes, it certainly does help to correct
the code a bit. Unfortunately though, these spreadsheets have already been sent out to the field, so it is difficult to either do a mass resend or walk them through fixing the code one by one. I was hoping that maybe there was some issue within excel that would be an easy fix. Guess not, thanks once again. "Jim Cone" wrote: SJC, Well you got me. However I did condense the code a little and commented out the paper size code line. It might be worth a try it to see if it works on one of the problem machines. Jim Cone San Francisco, USA '----------------------------- Sub January2006() ' January2006 Macro ' Macro recorded 3/23/2005 ActiveSheet.Range("A1:L13").Select Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlAll ActiveCell.Offset(7, 0).EntireRow.RowHeight = 69 Selection.Interior.ColorIndex = xlNone ActiveWindow.DisplayGridlines = False ActiveWorkbook.DisplayDrawingObjects = xlHide ActiveCell.Offset(0, 1).EntireColumn.ColumnWidth = 21 With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" .PrintArea = "" .LeftMargin = 54 .RightMargin = 54 .TopMargin = 72 .BottomMargin = 72 .HeaderMargin = 36 .FooterMargin = 36 .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False ' .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 End With Dim strPath As String Dim strFileName As String Range("C2:F2").Select strPath = Application.DefaultFilePath & "\" strFileName = Range("C2").Value & "_January 2006 Report" ActiveWorkbook.SaveAs strPath & strFileName ActiveWindow.Close Sheets("Intro--Start Here").Select MsgBox ("The January 2006 file has been saved to your My Documents folder. " _ & vbCr & "Please email file to XXX") End Sub '------------------ "SJC" wrote in message Yes, all of the machines in our office have the same printer drivers and printers, yet some produce the error and some do not. "Jim Cone" wrote: SJC, Have you checked out if the machines throwing the error have printers that can handle letter size paper? Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "SJC" wrote in message ... I have created an excel spreadsheet for multiple users which enables them to record data monthly. I created a macro which then produces another excel file containing a report of this data, so that the user does not have to send me their entire spreadsheet, which is an enormous file. Being a beginner at VBA, I recorded this macro and tested it thoroughly, and it worked fine. Now that the spreadsheet is being used by over 200 users, the macro is producing an error for some users and works fine for other users. I have again tested the macro in our office, and it works on some machines and not for others. The error that is produced is "Runtime error 1004: Unable to set the Paper Size property of the Page Setup class." This spreadsheet was created in Excel 2000. All machines tested have Excel 2000 and Windows XP. So, is there an option or setting in Excel that is contriubting to this error? I have users working with this spreadsheet already in multiple US states, so I am hoping to find a solution that would be easy for them to fix without redistributing the entire file. These users would also not be candidates for fixing the code themselves. I have included the code below. Thanks so much for any insight. Sub January2006() ' ' January2006 Macro ' Macro recorded 3/23/2005 ' ActiveSheet.Range("A1:L13").Select Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=False ActiveCell.Offset(7, 0).Rows("1:1").EntireRow.RowHeight = 69 Selection.Interior.ColorIndex = xlNone ActiveWindow.DisplayGridlines = False ActiveWorkbook.DisplayDrawingObjects = xlHide ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 15.57 ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 21 With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 End With Dim strPath As String Dim strFileName As String Range("C2:F2").Select strPath = Application.DefaultFilePath & "\" strFileName = Range("C2").Value & "_January 2006 Report" ActiveWorkbook.SaveAs strPath & strFileName, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ActiveWindow.Close Sheets("Intro--Start Here").Select MsgBox ("The January 2006 file has been saved to your My Documents folder. Please email file to XXX") End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro VBA code to name Save-As file | Excel Worksheet Functions | |||
| Macro for changing text to Proper Case | Excel Worksheet Functions | |||
| Using other workbooks.. | Excel Worksheet Functions | |||
| Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
| macro code shortcut | Excel Worksheet Functions | |||
Hybrid Mode
