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

Do you mean the Excel program

ActiveWorkbook.SaveAs Filename:= _
Application.Path & Application.PathSeparator & "meter_readings.xml"
_
, FileFormat:=xlUnicodeText, CreateBackup:=False

or this workbook


ActiveWorkbook.SaveAs Filename:= _
ThisWorkbook.Path & Application.PathSeparator & "meter_readings.xml"
_
, FileFormat:=xlUnicodeText, CreateBackup:=False



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Barry" wrote in message
...
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

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

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

I have several questions

1) What is the activeworkbook path?
2) What path do you want it saved to?
3) How do you determine that path?

Thanks,
Barb Reinhardt



"Barry" wrote:

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

I can't answer your question directly, because the program will be run on
OTHER PCs and may therefore be in a different subdirectory for each PC (in
fact a user may need to have more than one copy, each in a different
directory.
The requirement is to save the files to the SAME directory as the excel
program that's being executed ( ie the 'current' directory).

As an test/example, try creating an Excel file (with my Macro in it) and put
it in a subdirectory somewhere ... then run the macro. On my PC ... the
excel file gets saved in the correct place (overwriting the existing file)
put the other two files don't get written to the same directory.

--
Thanks ... Barry


"Barb Reinhardt" wrote:

I have several questions

1) What is the activeworkbook path?
2) What path do you want it saved to?
3) How do you determine that path?

Thanks,
Barb Reinhardt



"Barry" wrote:

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

OK, I think I understand what you want, but I want to verify.

1) The macro is NOT in the active workbook
2) You want the saved files to be saved in the same folder as the workbook
with the macros.

Let me know,
Barb Reinhardt



"Barry" wrote:

I can't answer your question directly, because the program will be run on
OTHER PCs and may therefore be in a different subdirectory for each PC (in
fact a user may need to have more than one copy, each in a different
directory.
The requirement is to save the files to the SAME directory as the excel
program that's being executed ( ie the 'current' directory).

As an test/example, try creating an Excel file (with my Macro in it) and put
it in a subdirectory somewhere ... then run the macro. On my PC ... the
excel file gets saved in the correct place (overwriting the existing file)
put the other two files don't get written to the same directory.

--
Thanks ... Barry


"Barb Reinhardt" wrote:

I have several questions

1) What is the activeworkbook path?
2) What path do you want it saved to?
3) How do you determine that path?

Thanks,
Barb Reinhardt



"Barry" wrote:

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