Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How write Macro to save worksheet to a RELATIVE address
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How write Macro to save worksheet to a RELATIVE address
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How write Macro to save worksheet to a RELATIVE address
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How write Macro to save worksheet to a RELATIVE address
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How write Macro to save worksheet to a RELATIVE address
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How write Macro to save worksheet to a RELATIVE address
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How write Macro to save worksheet to a RELATIVE address
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 with the macros -- Thanks ... Barry "Barb Reinhardt" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How write Macro to save worksheet to a RELATIVE address
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 -- HTH, Barb Reinhardt "Barry" wrote: 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 with the macros -- Thanks ... Barry "Barb Reinhardt" wrote: 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How write Macro to save worksheet to a RELATIVE address
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 -- Thanks ... Barry "Barb Reinhardt" wrote: 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 -- HTH, Barb Reinhardt "Barry" wrote: 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 with the macros -- Thanks ... Barry "Barb Reinhardt" wrote: 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How write Macro to save worksheet to a RELATIVE address
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. -- HTH, Barb Reinhardt "Barry" wrote: 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 -- Thanks ... Barry "Barb Reinhardt" wrote: 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 -- HTH, Barb Reinhardt "Barry" wrote: 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 with the macros -- Thanks ... Barry "Barb Reinhardt" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I write a request to 'Save As' Macro | Excel Discussion (Misc queries) | |||
How do I avoid excel change absolute address to relative address | Excel Discussion (Misc queries) | |||
How to write macro that selects relative locations | Excel Worksheet Functions | |||
what is difference between absolute address and relative address? | Excel Discussion (Misc queries) | |||
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? | Excel Worksheet Functions |