Resume.xlw and closing workbook with VBA code.

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Jim Cone]

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.

 

[Guest]

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]

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" <[email protected]>
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

 

[Guest]

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