Accessing *.bas files from within excel vba 1

[electricpete]

I am trying to access the vba version of alglib

http://www.alglib.net/

When I download and unzip, I see a bunch of bas files.

Is there a way to make these files available to excel vba other than cutting/pasting each one into a module?

 

[ThichTraXanh]

Yes.
1. Open excel
2. Press Alt + F11
3. Click Insert(menu) and select Module
4. New folder in Project Window with Module1
5. Right Click Module folder, Select Import File
6. Import your *.bas file

 

[electricpete]

Thanks. That reduces keystrokes, but I need a little more.

There are 101 bas files in the project. They are intertwined without a clear map of dependencies. So I would like to load them all. And once I do it, I don't want to have to do it again.

If I load them all into a spreadsheet once, can I think save it as an add-in so I won't have to do it ever again?

 

[electricpete]

Actually I tried creating an add-in as follows:
1 - imported several of the relevant modules.
2 - Saved spreadsheet as myaddin.xla
3 - Closed excel
4 - Created new spreadsheet. Added myaddin via excel add-in menu.
5 - Went to vba I.D.E and verified the Project myaddin was there, with all the modules I had previously crated
6 - Created a test module in the spreadsheet project. From the test module, none of the public declarations from the myaddin project are available (even though they are declared as public in the add-in).

Is public declaration limited to the add-in level. I don't want to intermix 101 modules with my own, so I'd like to have them in separate project. But doesn't seem to work when I tried it above using an add-in.

 

[electricpete]

I will go ahead and start a new thread, since it's a new question / new subject.
Thanks.

 

[electricpete]

Now I'm thinking about reducing the effort by adding all these modules programmatically.

I was able to add one module using:
application.modules.Add.InsertFile("C:\Documents and Settings\peter\My Documents\AlgLib\SourceBas\Spline1d.bas")

I was also able to rename the just-added module as follows:
application.modules.Add.InsertFile("C:\Documents and Settings\peter\My Documents\AlgLib\SourceBas\Spline1d.bas")
modules(modules.Count).name = "Spline1d"

Now what I'd really like to do is enter a directory, and have vba find all the files in the directory and perform these operations (add module and rename it). I'm sure it can be done, but I don't know much about how to get a collection of filenames to cycle thru.

 

[electricpete]

I was able to adapt some code from Walkenbach's book to automate the task as shown below:

Sub BatchProcess()
Dim FS As FileSearch
Dim FilePath As String, FileSpec As String
Dim I As Integer
Dim myfilename As String

' Specify path and file spec
FilePath = "C:\Documents and Settings\peter\My Documents\AlgLib\SourceBas\"
FileSpec = "*.bas"
' Create a FileSearch object
Set FS = Application.FileSearch
With FS
.LookIn = FilePath
.FileName = FileSpec
.Execute
' Exit if no files are found
If .FoundFiles.Count = 0 Then
MsgBox "No files were found"
Exit Sub
End If
End With
' Loop through the files and process them
For I = 1 To FS.FoundFiles.Count
Stop
Application.Modules.Add.InsertFile (FS.FoundFiles(I))
myfilename = "z_" & FileNameOnly(FS.FoundFiles(I))
myfilename = Mid(myfilename, 1, Len(myfilename) - 4)
Modules(Modules.Count).Name = myfilename

Next I
End Sub

Public Function FileNameOnly(pname) As String
' Returns the filename from a path/filename string
Dim I As Integer, length As Integer, Temp As String
length = Len(pname)
Temp = ""
For I = length To 1 Step -1
If Mid(pname, I, 1) = Application.PathSeparator Then
FileNameOnly = Temp
Exit Function
End If
Temp = Mid(pname, I, 1) & Temp
Next I
FileNameOnly = pname
End Function

It worked well to import all the modules, I think. I have only one small doubt remaining. The modules.count variable shows 101. modules(1) corresponds to the first file imported and modules(101) corresponds to the last. But there is also the module that I wrote my code in... should be 102 I would think. I will investigate further.

 

[electricpete]

Hmmm. If I create a brand new spreadsheet, insert one module, put a simple sub in there (helloworld), and query modules.count variable, I get 0. Why the heck is that?

 

[electricpete]

I have only one small doubt remaining. The modules.count variable shows 101. modules(1) corresponds to the first file imported and modules(101) corresponds to the last. But there is also the module that I wrote my code in... should be 102 I would think. I will investigate further....

Hmmm. If I create a brand new spreadsheet, insert one module, put a simple sub in there (helloworld), and query modules.count variable, I get 0. Why the heck is that?

I think the answer to this is that modules.count is not really a count of the modules. Bill Gates' guys explain it here (I'm using MS Office 2000):

http://support.microsoft.com/kb/201471

 

[ThichTraXanh]

http://www.tek-tips.com/faqs.cfm?fid=4114

if you can use the open file dialog-box (single/multiple) then your program will be more flexible