View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
barry barry is offline
external usenet poster
 
Posts: 116
Default How write Macro to save worksheet to a RELATIVE address

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