Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resume.xlw and closing workbook with VBA code.
Hi, all.
I'm an Access programmer working on a .mdb that creates an Excel spreadsheet. After doing so, I need to open the worksheet, edit it to set consistent heading formats, then close it. The spreadsheet creates beautifully, and all of the formatting that I want is applied to it, but I have two glitches when trying to close it: 1) The .Close command generates a "method not supported for this object" error. 2) When I then try to close it manually, I get an error message that reads "A file named 'RESUME.XLW' already exists in this location. Do you want to replace it? I've only done fairly simple macros in Excel, usually creating them with recorder and making minor modifications, so I'm not familiar "resume.xlw". Only found a couple of posts dealing with it when I searched here, and they weren't much help. The sub is below. Can anyone out there help? Thanks, Bruce Sub SetSpreadsheetHeadings(forFilePath) On Error GoTo SpreadsheetErr ' ' Sets headings for new spreadsheet. ' Dim MyXL As Object Set MyXL = CreateObject("Excel.Application") With MyXL .Workbooks.Open forFilePath 'I'd rather not make it visible at all, if I can do it that way. .Application.Visible = True .Parent.Windows(1).Visible = True .Rows("1:1").Select .Selection.Font.FontStyle = "Bold" .Selection.Borders(xlDiagonalDown).LineStyle = xlNone .Selection.Borders(xlDiagonalUp).LineStyle = xlNone .Selection.Borders(xlEdgeLeft).LineStyle = xlNone .Selection.Borders(xlEdgeTop).LineStyle = xlNone With .Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With .Selection.Borders(xlEdgeRight).LineStyle = xlNone .Selection.Borders(xlInsideVertical).LineStyle = xlNone With .Selection.Interior .ColorIndex = 15 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With .Cells.Select .Selection.Columns.AutoFit .Columns("A:A").Select .Selection.EntireColumn.Hidden = True 'Unhighlight rows left selected. .Range("B2").Select .Save DoEvents .Close 'Causes error. End With Set MyXL = Nothing Exit Sub SpreadsheetErr: MsgBox "Error editing spreadsheet:" & vbCr & vbCr & _ "Error Code: " & Err.Number & vbCr & _ Err.Description, vbOKCritical, "Error!" Err.Clear End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resume.xlw and closing workbook with VBA code.
I hope this is somehow helpful
xlw : you are creating a work space/ usually a workspace is created by clicking Tools and then shared workspace. It usually allows multi users to enter information at the same time. The interesting thing is that after the worksheets are hiperlinked any changes in a worksheet reflects in he workspace sheet which is the mastersheet. Example is EAST, NORTH, SOUTH, WEST, stores are on one worksheet in the vertical position East is (A) North is (B) on and on. In the horzional are each one of the sales people in that office and their sales figuers for their area. In the begining each store enters information on their excel template, all the templates are first is stored in "MY DOCUMENTS" then all information is transfered to one worksheet XLW and the hiperlinks are then created. So now your VBA code creates the workspace. I belive the statement "Workbooks" Now, you no longer need to open up another excel data sheet. The excel information is linked and changes to the slave creates changes to to the master. I have never used this function from a data base so I cant help you write a VBA script that will close the master doc. I guess my question, (have the hiperlinks been created) are you able to make changes in Access data, and they are reflected in Excel? When you say it works perfectly, is that what you ment. "BruceS" wrote: Hi, all. I'm an Access programmer working on a .mdb that creates an Excel spreadsheet. After doing so, I need to open the worksheet, edit it to set consistent heading formats, then close it. The spreadsheet creates beautifully, and all of the formatting that I want is applied to it, but I have two glitches when trying to close it: 1) The .Close command generates a "method not supported for this object" error. 2) When I then try to close it manually, I get an error message that reads "A file named 'RESUME.XLW' already exists in this location. Do you want to replace it? I've only done fairly simple macros in Excel, usually creating them with recorder and making minor modifications, so I'm not familiar "resume.xlw". Only found a couple of posts dealing with it when I searched here, and they weren't much help. The sub is below. Can anyone out there help? Thanks, Bruce Sub SetSpreadsheetHeadings(forFilePath) On Error GoTo SpreadsheetErr ' ' Sets headings for new spreadsheet. ' Dim MyXL As Object Set MyXL = CreateObject("Excel.Application") With MyXL .Workbooks.Open forFilePath 'I'd rather not make it visible at all, if I can do it that way. .Application.Visible = True .Parent.Windows(1).Visible = True .Rows("1:1").Select .Selection.Font.FontStyle = "Bold" .Selection.Borders(xlDiagonalDown).LineStyle = xlNone .Selection.Borders(xlDiagonalUp).LineStyle = xlNone .Selection.Borders(xlEdgeLeft).LineStyle = xlNone .Selection.Borders(xlEdgeTop).LineStyle = xlNone With .Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With .Selection.Borders(xlEdgeRight).LineStyle = xlNone .Selection.Borders(xlInsideVertical).LineStyle = xlNone With .Selection.Interior .ColorIndex = 15 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With .Cells.Select .Selection.Columns.AutoFit .Columns("A:A").Select .Selection.EntireColumn.Hidden = True 'Unhighlight rows left selected. .Range("B2").Select .Save DoEvents .Close 'Causes error. End With Set MyXL = Nothing Exit Sub SpreadsheetErr: MsgBox "Error editing spreadsheet:" & vbCr & vbCr & _ "Error Code: " & Err.Number & vbCr & _ Err.Description, vbOKCritical, "Error!" Err.Clear End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resume.xlw and closing workbook with VBA code.
Sunday,
Thanks for replying! I was able to fix the error when closing by using info in a post I found on the Access group. I was leaving out "ActiveWorkbook". The correct format is "MyXL.ActiveWorkbook.Close False" followed by "MyXL.Quit" Still having the "Resume.xlw" error message problem, though. The spreadsheet is not "linked" (intentionally, anyway) to the Access database. It is created by using the TransferSpreadsheet command in VBA and is the output of a table created by my routine. When I say it works perfectly, I mean that the spreadsheet is created in the location desired and all of the formatting I want is applied to it. Based upon what you said below, I'm guessing that I have to either create the spreadsheet differently or open it for editing differently. Any ideas? Thanks, Bruce "Sunday88310" wrote: I hope this is somehow helpful xlw : you are creating a work space/ usually a workspace is created by clicking Tools and then shared workspace. It usually allows multi users to enter information at the same time. The interesting thing is that after the worksheets are hiperlinked any changes in a worksheet reflects in he workspace sheet which is the mastersheet. Example is EAST, NORTH, SOUTH, WEST, stores are on one worksheet in the vertical position East is (A) North is (B) on and on. In the horzional are each one of the sales people in that office and their sales figuers for their area. In the begining each store enters information on their excel template, all the templates are first is stored in "MY DOCUMENTS" then all information is transfered to one worksheet XLW and the hiperlinks are then created. So now your VBA code creates the workspace. I belive the statement "Workbooks" Now, you no longer need to open up another excel data sheet. The excel information is linked and changes to the slave creates changes to to the master. I have never used this function from a data base so I cant help you write a VBA script that will close the master doc. I guess my question, (have the hiperlinks been created) are you able to make changes in Access data, and they are reflected in Excel? When you say it works perfectly, is that what you ment. "BruceS" wrote: Hi, all. I'm an Access programmer working on a .mdb that creates an Excel spreadsheet. After doing so, I need to open the worksheet, edit it to set consistent heading formats, then close it. The spreadsheet creates beautifully, and all of the formatting that I want is applied to it, but I have two glitches when trying to close it: 1) The .Close command generates a "method not supported for this object" error. 2) When I then try to close it manually, I get an error message that reads "A file named 'RESUME.XLW' already exists in this location. Do you want to replace it? I've only done fairly simple macros in Excel, usually creating them with recorder and making minor modifications, so I'm not familiar "resume.xlw". Only found a couple of posts dealing with it when I searched here, and they weren't much help. The sub is below. Can anyone out there help? Thanks, Bruce Sub SetSpreadsheetHeadings(forFilePath) On Error GoTo SpreadsheetErr ' ' Sets headings for new spreadsheet. ' Dim MyXL As Object Set MyXL = CreateObject("Excel.Application") With MyXL .Workbooks.Open forFilePath 'I'd rather not make it visible at all, if I can do it that way. .Application.Visible = True .Parent.Windows(1).Visible = True .Rows("1:1").Select .Selection.Font.FontStyle = "Bold" .Selection.Borders(xlDiagonalDown).LineStyle = xlNone .Selection.Borders(xlDiagonalUp).LineStyle = xlNone .Selection.Borders(xlEdgeLeft).LineStyle = xlNone .Selection.Borders(xlEdgeTop).LineStyle = xlNone With .Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With .Selection.Borders(xlEdgeRight).LineStyle = xlNone .Selection.Borders(xlInsideVertical).LineStyle = xlNone With .Selection.Interior .ColorIndex = 15 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With .Cells.Select .Selection.Columns.AutoFit .Columns("A:A").Select .Selection.EntireColumn.Hidden = True 'Unhighlight rows left selected. .Range("B2").Select .Save DoEvents .Close 'Causes error. End With Set MyXL = Nothing Exit Sub SpreadsheetErr: MsgBox "Error editing spreadsheet:" & vbCr & vbCr & _ "Error Code: " & Err.Number & vbCr & _ Err.Description, vbOKCritical, "Error!" Err.Clear End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resume.xlw and closing workbook with VBA code.
What is the file suffix of "forFilePath"?
A normal Excel worksheet has an .xls extension not a .xlw extension. The application does not have to be visible to work in it. You can verify if Excel actually quits by checking Task Manager. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resume.xlw and closing workbook with VBA code.
Jim,
"forFilePath" is a variable containing the full path and file name of the spreadsheet, e.g. "C:\Temp\TestExport.xls" Per second post, have fixed problem with closing spreadsheet but I am still seeing dialog box about overwriting the "Resume.xlw" file. Bruce "Jim Cone" wrote: What is the file suffix of "forFilePath"? A normal Excel worksheet has an .xls extension not a .xlw extension. The application does not have to be visible to work in it. You can verify if Excel actually quits by checking Task Manager. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resume.xlw and closing workbook with VBA code.
I would run a file search and determine if you have any .xlw files on your system,
particularly in the Excel Start folder. I reviewed the limited number of newsgroup posts relating to Resume.xlw and saw a couple from six or seven years ago saying this could be virus related??? Also, to eliminate application messages when running code you could use... MyXL.DisplayAlerts = False. This must be set back to True before quitting the application. Humor me, check Task Manager for Excel.exe after your code completes. -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "BruceS" wrote in message Jim, "forFilePath" is a variable containing the full path and file name of the spreadsheet, e.g. "C:\Temp\TestExport.xls" Per second post, have fixed problem with closing spreadsheet but I am still seeing dialog box about overwriting the "Resume.xlw" file. Bruce "Jim Cone" wrote: What is the file suffix of "forFilePath"? A normal Excel worksheet has an .xls extension not a .xlw extension. The application does not have to be visible to work in it. You can verify if Excel actually quits by checking Task Manager. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resume.xlw and closing workbook with VBA code.
Jim,
Found it!! Another newsgroup recommended specifying the workbook, not just "ActiveWorkbook", in the .Close. Doing so eliminated the "resume.xlw" dialog box. For your humor, no, there was no instance of Excel showing in the Task Manager. What's wierd, though, is that when Access generated an error and left the workbook open, it was not in Task Manager, either. (I had both the Excel app and the workbook not visible.) When I double-clicked the file, it would ask about opening a second one and if I wanted to loose my changes to the first, then it would flash the spreadsheet up and re-hide it. Could not delete, overwrite, etc. Only way to get access to it was to re-boot. Thanks for your time! Bruce "Jim Cone" wrote: I would run a file search and determine if you have any .xlw files on your system, particularly in the Excel Start folder. I reviewed the limited number of newsgroup posts relating to Resume.xlw and saw a couple from six or seven years ago saying this could be virus related??? Also, to eliminate application messages when running code you could use... MyXL.DisplayAlerts = False. This must be set back to True before quitting the application. Humor me, check Task Manager for Excel.exe after your code completes. -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "BruceS" wrote in message Jim, "forFilePath" is a variable containing the full path and file name of the spreadsheet, e.g. "C:\Temp\TestExport.xls" Per second post, have fixed problem with closing spreadsheet but I am still seeing dialog box about overwriting the "Resume.xlw" file. Bruce "Jim Cone" wrote: What is the file suffix of "forFilePath"? A normal Excel worksheet has an .xls extension not a .xlw extension. The application does not have to be visible to work in it. You can verify if Excel actually quits by checking Task Manager. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
close workbook without closing excel and stop recursive function | Excel Discussion (Misc queries) | |||
Opened Autosave but when closing workbook Autosave closes itself | Excel Worksheet Functions | |||
Help with closing down a workbook?? | Excel Worksheet Functions | |||
prohibit closing a workbook | Excel Discussion (Misc queries) | |||
Stubborn toolbars in Excel | Excel Discussion (Misc queries) |