Thanks Barb, but bad news ...
When I run your macro on my PC (Excel 2000 v 9.0)
the spreadsheet gets saved to the current directory, but both workpages get
saved to My Documents.

Any other suggestions?


"Barb Reinhardt" wrote:

I tweaked it some. Try this

Sub Macro2()
'
Dim aWB As Workbook
Dim awbPath As String
Dim myWS As Worksheet

Set aWB = ActiveWorkbook
aWB.Save
awbPath = aWB.Path

MsgBox "Note: The current Spreadsheet has been automatically saved .. as
" & vbNewLine & _
"the name will now be changed by the program."
'
'
Set myWS = aWB.Sheets("meter_readings.xml")

myWS.Select
aWB.SaveAs Filename:= _
"meter_readings.xml" _
, FileFormat:=xlUnicodeText, CreateBackup:=False


Set myWS = aWB.Sheets("meter_readings.html")
myWS.Select
myWS.Range("A1:B10").Select
myWS.Range("B10").Activate
aWB.PublishObjects.Add(SourceType:=xlSourceRange, _
Filename:=myWS.Name, _
Sheet:=myWS.Name, _
Source:="$A$1:$B$10", _
HtmlType:=xlHtmlStatic, _
DivID:=" meter_readings_19233", _
Title:="").Publish
'
MsgBox "Note: Both files have now been saved to the current " &
vbNewLine & _
"subdirectory. The Spreadsheet WILL now be closed without further
saving ..." & vbNewLine & _
"(as the name has been changed by the program)"
'
aWB.Close SaveChanges:=False
End Sub



--
HTH,
Barb Reinhardt



"Barry" wrote:

I have recorded a Macro to save a worksheet. Then I edited the macro to
change the ABSOLUTE file/address to a relative address (I want to save the
files in the same folder as the Excel program being run Nb It will run on
many different PCs), but the results seem 'unpreditable', sometimes when it
runs it writes them to the correct place and sometimes to 'MyDocument'?

Any ideas gratefully appreciated. My code is as follows:

Sub Macro2()
'
ActiveWorkbook.Save
MsgBox "Note: The current Spreadsheet has been automatically saved .. as
the name will now be changed by the program."
'
'
Sheets("meter_readings.xml").Select
ActiveWorkbook.SaveAs Filename:= _
"meter_readings.xml" _
, FileFormat:=xlUnicodeText, CreateBackup:=False

Sheets("meter_readings.html").Select
Range("A1:B10").Select
Range("B10").Activate
ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"meter_readings.html" _
, "meter_readings.html", "$A$1:$B$10", xlHtmlStatic,
"meter_readings_19233", "" _
).Publish (True)

'
MsgBox "Note: Both files have now been saved to the current
subdirectory. The Spreadsheet WILL now be closed without further saving ...
(as the name has been changed by the program)"
'
Workbooks("meter_readings.xml").Close SaveChanges:=False
End Sub


--
Thanks ... Barry