Import all 'bas' files to a new excel spreadsheet using VBA

[excelhelp]

Hi

Following on from this thread, is there a way you can

Import all 'bas' files in a network folder to a new excel spreadsheet.

If you have say 30 modules in one spreadsheet and you want to export them all to another spreadsheet,

I have the code below, and I get an error in the line:

fn.FoundFiles.Import _
Filename:="L:" & "" & "book2" & ".xls"


Sub ImportAllVBA()

'Need to change code


Dim fs As FileSearch, ws As Worksheet, i, j As Long
Dim f, mySubFolders, aFolder
'Dim VBComp As VBIDE.VBComponent
Set fs = Application.FileSearch

With fs
.SearchSubFolders = False ' set to true if you want sub-folders included
' .FileType = msoFileTypeAllFiles 'This lists all files


fn = InputBox("Which directory do you want files from?", , "L:\VBAcode")
.LookIn = fn
'.LookIn = "L:" 'modify this to where you want to search
If .Execute > 0 Then

'Set ws = Worksheets.Add
Workbooks("book2.xls").Activate

For i = 1 To .FoundFiles.Count

fn.FoundFiles.Import _
Filename:="L:" & "\" & "book2" & ".xls"

Next

End If

End With

End Sub

 

[PHV]

Straight from the VBA help:
Application.VBE.ActiveVBProject.VBComponents.Import("test.bas")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

'FoundFiles' returns a collection of strings containing full names of found files. There is no 'Import' method for 'FoundFiles', see the class properties in object browser.
It is another story to cope with found files. You need VBIDE (Microsoft Visual Basic for Applications Extensibility 5.3) referenced with programmatic access to VBProject (xp+) to import found files to excel file.

combo

 

[excelhelp]

Combo

I do have the following:

You need VBIDE (Microsoft Visual Basic for Applications Extensibility 5.3) referenced with programmatic
'access to VBProject (xp+) to import found files to excel file.

I checked in Tools, references.

If possible, please could you amend my code so that I can umport all 'bas' files from a folder.

 

[combo]

FoundFiles(i) returns path & name of i-th found file. Apply it in PH's code. See the excel object model (excel vba help) to find the path to given workbook's vbproject.

combo