Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
include VBA macros as text file
Hi there,
I have a strange requirement. I am not sure if it is feasible. I would be very greatful if someone could provide a solution for this problem. I have to generate a file. This file would have a "command" or "code" in the first cell A1. When the user opens the file, the "Auto_Open" macro within the file would check this code and expand it as required. Now the problem is that, the file has to be editable by another program, which would modify the cell A1 and insert the correct code. But this program could work only with text files. I am aware that excel can process XML, CSV files (which are text file) and read in the data. My question is, is it somehow possible to include macros in XML file ? Or is there a better way to acheive this ? Thanks a lot in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
include VBA macros as text file
Suresh,
If I understand your request, you want another program besides excel to create a text file that is a VBA macro. An option for you may be to use VB script. VB script is like VBA but a little less function and it allows you to control excel from outside of excel. Here is an example to get you started: dim ExcelApp, ExcelWB, ExcelWS set ExcelApp = createobject("Excel.Application") set ExcelWB = ExcelApp.Workbooks.Open("C:\MyFile.xls") set ExcelWS = ExcelWB.Worksheets("MyWorksheet") ExcelWB.Range("A1") = "Some value" set ExcelWS = Nothing set ExcelWB = Nothing ExcelApp.Close This is all untested. Save the file as a .vbs file and run. Hope that's what you were looking for. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
include VBA macros as text file
"Kletcho" wrote in message oups.com... Suresh, If I understand your request, you want another program besides excel to create a text file that is a VBA macro. An option for you may be to use VB script. VB script is like VBA but a little less function and it allows you to control excel from outside of excel. Here is an example to get you started: dim ExcelApp, ExcelWB, ExcelWS set ExcelApp = createobject("Excel.Application") set ExcelWB = ExcelApp.Workbooks.Open("C:\MyFile.xls") set ExcelWS = ExcelWB.Worksheets("MyWorksheet") ExcelWB.Range("A1") = "Some value" set ExcelWS = Nothing set ExcelWB = Nothing ExcelApp.Close This is all untested. Save the file as a .vbs file and run. Hope that's what you were looking for. Thankyou very much. Thats exactly what I was looking for. hmm ... that means .... I will have to learn some VB Scripting now .. :-S |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
include VBA macros as text file
Yes you can include Excel macros stored in a text file at runtime: e.g.
application.VBE.ActiveVBProject.VBComponents.Item( "ThisWorkbook").CodeModule.AddFromFile "c:\myxl.txt" This adds all the code from c:\myxl.txt into the ThisWorkbook class/module. Thereafter, you can call the macros as you would normally. PROBLEM: Once added, the code becomes permanent as soon as the workbook is saved, unless you remove the code. Is is Chip Pearson's site that gives details of how this is done? Someone with better memory will guide you. "Suresh" wrote: Hi there, I have a strange requirement. I am not sure if it is feasible. I would be very greatful if someone could provide a solution for this problem. I have to generate a file. This file would have a "command" or "code" in the first cell A1. When the user opens the file, the "Auto_Open" macro within the file would check this code and expand it as required. Now the problem is that, the file has to be editable by another program, which would modify the cell A1 and insert the correct code. But this program could work only with text files. I am aware that excel can process XML, CSV files (which are text file) and read in the data. My question is, is it somehow possible to include macros in XML file ? Or is there a better way to acheive this ? Thanks a lot in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
include VBA macros as text file
VBA can import code from a text file like this:
Sub test() Dim VBProj As VBProject Dim VBComp As VBComponent Dim VBCodeMod As CodeModule Dim strFile As String Set VBProj = ThisWorkbook.VBProject Set VBComp = VBProj.VBComponents("SubsFromFile") Set VBCodeMod = VBComp.CodeModule strFile = "C:\codetester.txt" VBCodeMod.AddFromFile strFile End Sub But because you alter the VBE the workbook will need to re-compile. It will be much simpler to read a text file, but don't import it in the VBE. Just get the needed data from that file and run a Sub according to that data. The other program can edit the text file. RBS "Suresh" <no-emails wrote in message ... Hi there, I have a strange requirement. I am not sure if it is feasible. I would be very greatful if someone could provide a solution for this problem. I have to generate a file. This file would have a "command" or "code" in the first cell A1. When the user opens the file, the "Auto_Open" macro within the file would check this code and expand it as required. Now the problem is that, the file has to be editable by another program, which would modify the cell A1 and insert the correct code. But this program could work only with text files. I am aware that excel can process XML, CSV files (which are text file) and read in the data. My question is, is it somehow possible to include macros in XML file ? Or is there a better way to acheive this ? Thanks a lot in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can macros include page setup functions? | Excel Discussion (Misc queries) | |||
How do I set the save default not to include .xls in name of file | Excel Discussion (Misc queries) | |||
include macros in another macro | Excel Programming | |||
Can i include a vbscript file with an xml spreadsheet? | Excel Discussion (Misc queries) | |||
Include external file .BAS in Excel VBA | Excel Programming |