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

Posted on 2009-04-24
Last Modified: 2012-05-06
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


Question by:natdacruz
    LVL 13

    Expert Comment

    by:Brian Withun
    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


    Author Comment

    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 :)
    LVL 85

    Accepted Solution

    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)


    Author Comment

    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?

    Author Comment

    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

    Author Comment

    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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now