Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA text file to .xls
Suppose I were to create a text file containing VBA using
some text processing program (not connected with VBA, Excel, etc.). The VBA program that is output, when run in Excel will create a spreadsheet data and all. This would be done on a regular basis, i.e. its not a one time item. What is the easiest procedure to tell a user in order to give them the VBA text file so that they can read it in and run it. They are not necessarily sophisticated so its preferable if they have minimal effort to do this. Also note that the VBA text file may be long. What I really want is something like File | Open | select the VBA text file and it gets read in and executed but I assume that that would not work. One other possibility is that to produce a procedure to automatically read in the text file, execute the read-in VBA program and then save it as an .xls file (without the VBA program that generated it). In this case one could just give the .xls file to the user. Since this is all done on a regular basis this reading in and saving would have to be done without manual intervention. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA text file to .xls
Thanks. I am sure you are right about the desirability of providing
an .xls rather than .vba file. This seems like it might be a common requirement since it effectively allows one to create an Excel spreadsheet from any language that can output text, so I was wondering if such a utility already exists? That is, it would be called from the command line like this: vba2xls abc.vba abc.xls where abc.vba is a text file holding vba code that will construct a spreadsheet and abc.xls is the name to give the xls file that is so constructed. This utility would automatically run Excel, read in abc.vba, run the code it just read in and finally save the result to abc.xls (and preferably not save the abc.vba code which generated it). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA text file to .xls
Something from Chip Pearson: http://www.cpearson.com/excel/vbe.htm
Exporting All Modules In A Project The procedure below will list export all of the modules in a workbook to text files. It will save the files in the same folder as the workbook. This can be useful for saving a backup copy of your VBA, or for transferring VBA code from one project to another. Sub ExportAllVBA() Dim VBComp As VBIDE.VBComponent Dim Sfx As String For Each VBComp In ActiveWorkbook.VBProject.VBComponents Select Case VBComp.Type Case vbext_ct_ClassModule, vbext_ct_Document Sfx = ".cls" Case vbext_ct_MSForm Sfx = ".frm" Case vbext_ct_StdModule Sfx = ".bas" Case Else Sfx = "" End Select If Sfx < "" Then VBComp.Export _ Filename:=ActiveWorkbook.Path & "\" & VBComp.Name & Sfx End If Next VBComp End Sub -- steveB Remove "AYN" from email to respond wrote in message oups.com... Thanks. I am sure you are right about the desirability of providing an .xls rather than .vba file. This seems like it might be a common requirement since it effectively allows one to create an Excel spreadsheet from any language that can output text, so I was wondering if such a utility already exists? That is, it would be called from the command line like this: vba2xls abc.vba abc.xls where abc.vba is a text file holding vba code that will construct a spreadsheet and abc.xls is the name to give the xls file that is so constructed. This utility would automatically run Excel, read in abc.vba, run the code it just read in and finally save the result to abc.xls (and preferably not save the abc.vba code which generated it). |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA text file to .xls
Steve Bell posted the opposite of what you want. He saves the modules in a .xls
workbook to text files. (You want to import the text files, right?) But that link that Steve gave has code that can import text files into a workbook's project. I don't think that this is common at all, but that depends on your definition of common, I guess. I took some code from Chip's site and modified it slightly. Option Explicit Sub CopyOneModule() Dim wkbk As Workbook Dim FName As String Set wkbk = Workbooks.Add(1) FName = "c:\module1.bas" wkbk.VBProject.VBComponents.Import FName Application.DisplayAlerts = False wkbk.SaveAs Filename:="C:\myfile.xls", FileFormat:=xlWorkbookNormal wkbk.Close savechanges:=False End Sub This creates a new workbook, imports a text file and saves the workbook. So the code isn't that difficult. But one of the reasons I recommend you doing it once instead of everyone do it once is that there are things that can go wrong. One security level that was added in xl2002 can stop this kind of code from running. This is a user by user setting. And why bother asking some unsophisticated users to do this? I would think that if you're the developer, you should be doing the heavy lifting. So either you'll have to do the work or be prepared to answer questions why it doesn't work. (Heck, maybe you could have the thing that creates the text file create the .xls file, too???) ps. Check Chip's site for lots more things you can do in the VBE. wrote: Thanks. I am sure you are right about the desirability of providing an .xls rather than .vba file. This seems like it might be a common requirement since it effectively allows one to create an Excel spreadsheet from any language that can output text, so I was wondering if such a utility already exists? That is, it would be called from the command line like this: vba2xls abc.vba abc.xls where abc.vba is a text file holding vba code that will construct a spreadsheet and abc.xls is the name to give the xls file that is so constructed. This utility would automatically run Excel, read in abc.vba, run the code it just read in and finally save the result to abc.xls (and preferably not save the abc.vba code which generated it). -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA text file to .xls
Thanks. I'll have a look.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I import text file of cash flow to excel file then use formula | Excel Discussion (Misc queries) | |||
How to tell number of spaces between values in saved text file fromthe original xls file | Excel Discussion (Misc queries) | |||
How do I import text file, analyze data, export results, open next file | Excel Programming | |||
Excel VBA - open text file, replace text, save file? | Excel Programming | |||
importing text file, removing data and outputting new text file | Excel Programming |