How write Macro to save worksheet to a RELATIVE address

[Barry]

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

 

[Bob Phillips]

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)

 

[Barb Reinhardt]

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

 

[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]

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]

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.

 

[Barb Reinhardt]

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]

Thanks for your continued help Barb,

1) The macro IS in the active workbook
2) Yes, I want the saved files to be saved in the same folder as the workbook

 

[Barb Reinhardt]

Based on what you've told me about functionality, I don't think that the
macro is in the activeworkbook when you get to this part of the code. Try
this

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

myPath = ThisWorkbook.Path

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:= _
myPath & "\" & "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:=myPath & "\" & 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

 

[Barry]

Thanks Barb ... it WORKS ;-)

It looks like I was going down the wrong track with absolute v relative
referencing.

From what you write ... I guess that when one uses "SaveAs" - the active
workbook changes and thus the macro is in the 'old' workbook, but we are now
in the new 'active' workbook?

Can you suggest somewhere that I can read & learn about the topic.

Either way .... thanks VERY much for you time and help - much appreciated

 

[Barb Reinhardt]

I'm guessing that you have code to open another workbook in your macro. I'd
put some debug.print statements around that to determine where the
activeworkbook changes.

I tend to define variables (aWB, oWB, myWB, something like that) so that I
don't have to worry if the workbook I want to take action on is active or
not. I may set oWB as

Set oWB = workbooks.open(Filename)

I'm not sure where you'd read more about this. I'm entirely self taught
(mostly through this newsgroup). I should have a couple of Excel 2007 books
arriving in the mail today, but these are the first I've had.