Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1852
  • Last Modified:

excel vba - export import bas module as bas file - Run-Time error 91 - Subscript out of range - trying to set Workbook as filename and path

Im stuck,

Apart from locking a file I've spent a good few nights working on (by duplicating a Sub name in a module - sleep deprivation is never a good thing) I find my self stuck setting an application.workbooks(filename&path)

I have tried a number of combinations of the folder/filepath etc but no joy

I have added the code if anyone can spot the error Ive points a plenty - Im sure its something vsimple... as usual

also if anyone knows how to uncrash an excel file that is well and truly skuppered - ie it wont open at all (when you open it in Visual Basic Editor - it freezes as soon as th code window with the duplicated sub name pops up - so no guesses where the problem is there)
Points galore for that one too :)

Dealines are looming and sleep still evades ... all help gratefully accepted...

code is:

Option Explicit

Sub CopyModule()
' copies a module from one workbook to another
' example:
Dim strFolder As String
Dim strTempFile As String
Dim wb1 As Excel.Application
Dim wb2 As Excel.Application
Dim TargetFile As String
Dim SourceFile As String
Dim TargetWB As Workbook
Dim SourceWB As Workbook
Dim strModuleName As String
Dim strModuleName1 As String
Dim strModuleName2 As String
Dim strModuleName3 As String
Dim strModuleName4 As String
Dim strModuleName5 As String
Dim strModuleName6 As String
Dim module As Integer

SourceFile = "TS4.0 Control Panel - Backup.xls"
TargetFile = "Code Dump.xls"

Set wb1 = CreateObject("Excel.Application")
Set wb2 = CreateObject("Excel.Application")

    strFolder = "C:\Users\lrc\Desktop\TS4.0 Stable"
    Set TargetWB = wb1.Workbooks(strFolder & "\" & TargetFile)
    Set SourceWB = wb2.Workbooks(strFolder & "\" & SourceFile)

    strModuleName1 = "format_Control"
    strModuleName2 = "format_Extracts"
    strModuleName3 = "reportControl"
    strModuleName4 = "format_Control"
    strModuleName5 = "setParameters"
    strModuleName6 = "Module1"
'    If Len(strFolder) = 0 Then strFolder = CurDir

For module = 1 To 6

strModuleName = "strModuleName" & module

    strFolder = strFolder & "\"
    strTempFile = strFolder & strModuleName & ".bas"
    On Error Resume Next
    SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
    TargetWB.VBProject.VBComponents.Import strTempFile
    Kill strTempFile
    On Error GoTo 0

Next module

MsgBox ("All Done")
End Sub


  • 4
1 Solution
Brian WithunCommented:
Is your TargetWB supposed to reference a new file, or an existing file?

If it's supposed to be a new file, I seem to recall that you CANNOT name a workbook UNLESS you save it.

By that I mean, it will be called "Book1" (or "Book2" or whichever next sequential number) and you can't change that name except by actually saving it to disk.

Try creating your workbook using Workbooks.Add(), and then later .SaveAs() at which time you can give it a filename and path.

Read up on Application.DisplayAlerts, if you want Excel to stop prompting the user for a filename.
Set NewBook = Workbooks.Add
    fName = Application.GetSaveAsFilename
Loop Until fName <> False
NewBook.SaveAs Filename:=fName

Open in new window

natdacruzAuthor Commented:
Hi bhwithun sorry for getting back to you so late... I have already created and saved the target file... its in the same folder and pathway as the operating excel workbook and the source workbook.
I could create a new file as I dont mind where the bas file ends up ... in fact the bas file can stay in the source file directory for me to import into the appropriate file later.
This is where it stings - I am still getting the same problem with finding the source file... which is essential.
To cut things short though.... if i cant open the file manualy - will I have the same problem when I try and extract the module as a bas file. I think I may have to go back the drawing board and re-write the code from scratch ... ho hum
let me know if you can think of anything :)
Rory ArchibaldCommented:
Your code does not appear to be opening the files. These lines:

 Set TargetWB = wb1.Workbooks(strFolder & "\" & TargetFile)
    Set SourceWB = wb2.Workbooks(strFolder & "\" & SourceFile)

should be:

 Set TargetWB = wb1.Workbooks.Open(strFolder & "\" & TargetFile)
    Set SourceWB = wb2.Workbooks.Open(strFolder & "\" & SourceFile)

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

natdacruzAuthor Commented:
Ok Update...
I have tried to run the code again with the following...
    Set TargetWB = wb1.Workbooks.Open(strFolder & "\" & TargetFile)
it works fine... the problem is that I dont want to open the source file ... i need to extract the modules WITHOUT openning it as opening runs VB editor (or some other module checker and freezes when it reaches my corrupted module)
I have found some code that can extract data (cell values) from a file without openning it... I am sure there must be a way to reference a workbook.VBProject.VBComponents without opening it...
any ideas?
natdacruzAuthor Commented:
Using the
I have extracted the cell values from the first sheet in the wb.... its only logical that i must be able to get access to the workbook.VBProject.VBComponents without opening the file...
i have pasted the code I have used to do this below:
ub GetDataDemo()
    Dim FilePath$, Row&, Column&, Address$
     'change constants & FilePath below to suit
    Const FileName$ = "TS4.0 Control Panel - Backup.xls"
    Const SheetName$ = "Control"
    Const NumRows& = 50
    Const NumColumns& = 15
    FilePath = ActiveWorkbook.Path & "\"
    Application.ScreenUpdating = False
    If Dir(FilePath & FileName) = Empty Then
        MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
        Exit Sub
    End If
    For Row = 1 To NumRows
        For Column = 1 To NumColumns
            Address = Cells(Row, Column).Address
            Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
        Next Column
    Next Row
    ActiveWindow.DisplayZeros = False
End Sub
Private Function GetData(Path, File, Sheet, Address)
    Dim Data$
    Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
    Range(Address).Range("A1").Address(, , xlR1C1)
    GetData = ExecuteExcel4Macro(Data) '<------------------------------------------------- this is magic button
End Function
any ideas... this is not looking simple
natdacruzAuthor Commented:
wow somehow it worked..... the code I used is:

Sub ExportCode()
Dim app As Excel.Application
Set app = New Excel.Application
Dim wb As Excel.Workbook
Set wb = Excel.Application.Workbooks.Add("C:\Users\lrc\Desktop\TS4.0 Stable\TS4.0 Control Panel - Backup.xls")
Dim strExt As String
Dim VBComp As Object
For Each VBComp In wb.VBProject.VBComponents
Select Case VBComp.Type
Case 2  ' Class module
strExt = ".cls"
Case 3  ' Form
strExt = ".frm"
Case 1  ' Standard module
strExt = ".bas"
Case 100  ' Document?
strExt = ".cls"
Case Else
Stop  ' What else is there?
strExt = ".cls"
End Select
VBComp.Export "C:\" & VBComp.Name & strExt
wb.Close False
End Sub
for some reason it managed to get the bas files out before it crashed.... now i just need to find a way to get the sheets out ...ok muchas gracias and points to each and all of you :)

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now