Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA - Need to split export/import function

Posted on 2010-08-31
24
Medium Priority
?
373 Views
Last Modified: 2012-08-14
Hi,

I have a function that I got from : http://www.cpearson.com/excel/vbe.aspx, which allows the user to export all modules from one workbook, and import them into the workbook that the macro is on.  This function works well, but I want to split it into 2 functions: One to export all the modules to a new Workbook.  Then another to import the modules back into the workbook.  See the code below, which has the export/import bound in one function.  Can anyone help me slipt these up?
0
Comment
Question by:NerishaB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 12
24 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33564871
When you say "One to export all the modules to a new Workbook" presumably you just mean one to export all the modules to text files (otherwise it's doing what you already have)?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33564877
PS: You also haven't posted the code you are actually using, or mentioned why you want to do this as two steps? (I'd also suggest you download Rob Bovey's Code Cleaner add-in as it does all this for you)
0
 

Author Comment

by:NerishaB
ID: 33564905
The reason I want to do this is because I want to be able to export all the modules to text files, or .bas files.  This should be stored on my local PC.  Then I want to import them into another new workbook, make any changes I need to, then when I am done, I want to be able to import the files back into the first file.  See attached, the current code I am using.
Sub ExportImport()
Dim ModName As String
Dim FromProj As VBIDE.VBProject
Dim ToProj As VBIDE.VBProject
Dim Overwite As Boolean

    Set ToProj = Application.Workbooks.Add("Temp001.xlsm").VBProject
    Set FromProj = Application.Workbooks("Oz_main.xls").VBProject

    For Each Modul In FromProj.VBComponents
       If ExportModule(Modul.Name, FromProj, True) = True Then
       End If
      Next
    
End Sub

Function CopyModule(ModuleName As String, _
    FromVBProject As VBIDE.VBProject, _
    ToVBProject As VBIDE.VBProject, _
    OverwriteExisting As Boolean) As Boolean
        
    Dim VBComp As VBIDE.VBComponent
    Dim FName As String
    Dim CompName As String
    Dim S As String
    Dim SlashPos As Long
    Dim ExtPos As Long
    Dim TempVBComp As VBIDE.VBComponent
    
    '''''''''''''''''''''''''''''''''''''''''''''
    ' Do some housekeeping validation.
    '''''''''''''''''''''''''''''''''''''''''''''
    If FromVBProject Is Nothing Then
        CopyModule = False
        Exit Function
    End If
    
    If Trim(ModuleName) = vbNullString Then
        CopyModule = False
        Exit Function
    End If
    
    If ToVBProject Is Nothing Then
        CopyModule = False
        Exit Function
    End If
    
    If FromVBProject.Protection = vbext_pp_locked Then
        CopyModule = False
        Exit Function
    End If
    
    If ToVBProject.Protection = vbext_pp_locked Then
        CopyModule = False
        Exit Function
    End If
    
    On Error Resume Next
    Set VBComp = FromVBProject.VBComponents(ModuleName)
    If Err.Number <> 0 Then
        CopyModule = False
        Exit Function
    End If
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' FName is the name of the temporary file to be
    ' used in the Export/Import code.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    FName = Environ("Temp") & "\" & ModuleName & ".bas"
    If OverwriteExisting = True Then
        ''''''''''''''''''''''''''''''''''''''
        ' If OverwriteExisting is True, Kill
        ' the existing temp file and remove
        ' the existing VBComponent from the
        ' ToVBProject.
        ''''''''''''''''''''''''''''''''''''''
        If Dir(FName, vbNormal + vbHidden + vbSystem) <> vbNullString Then
            Err.Clear
            Kill FName
            If Err.Number <> 0 Then
                CopyModule = False
                Exit Function
            End If
        End If
        With ToVBProject.VBComponents
            .Remove .Item(ModuleName)
        End With
    Else
        '''''''''''''''''''''''''''''''''''''''''
        ' OverwriteExisting is False. If there is
        ' already a VBComponent named ModuleName,
        ' exit with a return code of False.
        ''''''''''''''''''''''''''''''''''''''''''
        Err.Clear
        Set VBComp = ToVBProject.VBComponents(ModuleName)
        If Err.Number <> 0 Then
            If Err.Number = 9 Then
                ' module doesn't exist. ignore error.
            Else
                ' other error. get out with return value of False
                CopyModule = False
                Exit Function
            End If
        End If
    End If
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Do the Export and Import operation using FName
    ' and then Kill FName.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    FromVBProject.VBComponents(ModuleName).Export Filename:=FName
    
    '''''''''''''''''''''''''''''''''''''
    ' Extract the module name from the
    ' export file name.
    '''''''''''''''''''''''''''''''''''''
    SlashPos = InStrRev(FName, "\")
    ExtPos = InStrRev(FName, ".")
    CompName = Mid(FName, SlashPos + 1, ExtPos - SlashPos - 1)
    
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' Document modules (SheetX and ThisWorkbook)
    ' cannot be removed. So, if we are working with
    ' a document object, delete all code in that
    ' component and add the lines of FName
    ' back in to the module.
    ''''''''''''''''''''''''''''''''''''''''''''''
    Set VBComp = Nothing
    Set VBComp = ToVBProject.VBComponents(CompName)
    
    If VBComp Is Nothing Then
        ToVBProject.VBComponents.Import Filename:=FName
    Else
        If VBComp.Type = vbext_ct_Document Then
            ' VBComp is destination module
            Set TempVBComp = ToVBProject.VBComponents.Import(FName)
            ' TempVBComp is source module
            With VBComp.CodeModule
                .DeleteLines 1, .CountOfLines
                S = TempVBComp.CodeModule.Lines(1, TempVBComp.CodeModule.CountOfLines)
                .InsertLines 1, S
            End With
            On Error GoTo 0
            ToVBProject.VBComponents.Remove TempVBComp
        End If
    End If
    Kill FName
    CopyModule = True
End Function

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33564964
Where do you want to put the bas files specifically?
I don't really see why you wouldn't just edit them in place, but anyway, I think the one problem you might have is determining which bas files to reimport afterwards, unless you want to just reimport ALL the bas files in that folder and then delete them?
0
 

Author Comment

by:NerishaB
ID: 33564968
Ok, I have the export function, only, it only works if I open the Workbook that I want to export modules from first.  I need to find a way to have the macro automatically open the appropriate Workbook.  See attached code for Export function.
Sub ExportVBAFiles()
  Dim FromProj As VBIDE.VBProject
    
  Dim VBComp As VBComponent     'VBA module, form, etc...
  Dim strSavePath As String  'Path to save the exported files to

  strSavePath = ThisWorkbook.Path & "\_VBACode"
  
  ' If this folder doesn't exist, create it
  If Dir(strSavePath, vbDirectory) = "" Then
    MkDir strSavePath
  End If
  
  ' Get the VBA project
 ' Set pVBAProject = ThisWorkbook.VBProject
  Set FromProj = Application.Workbooks("Oz_main.xls").VBProject
  ' Loop through all the components (modules, forms, etc) in the VBA project
  For Each VBComp In FromProj.VBComponents
    Select Case VBComp.Type
    Case vbext_ct_StdModule
      VBComp.Export strSavePath & "\" & VBComp.Name & ".bas"
    Case vbext_ct_Document, vbext_ct_ClassModule
      ' ThisDocument and class modules
      VBComp.Export strSavePath & "\" & VBComp.Name & ".cls"
    Case vbext_ct_MSForm
      VBComp.Export strSavePath & "\" & VBComp.Name & ".frm"
    Case Else
      VBComp.Export strSavePath & "\" & VBComp.Name
    End Select
  Next
    MsgBox "VBA files have been exported to: " & strSavePath
End Sub

Open in new window

0
 

Author Comment

by:NerishaB
ID: 33565014
I want to import all the .bas files in that folder, and delete them from that folder.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33565039
What is the "appropriate workbook" to open?
0
 

Author Comment

by:NerishaB
ID: 33565071
The "appropriate Workbook", is a set Workbook that i am using.  In this case it is located at C:\Excel\Excel_modules.xls
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33565386
Try this:



Option Explicit
Sub ExportFiles()
    Dim wbkModules As Workbook
    Dim FromProj As VBIDE.VBProject, ToProj As VBIDE.VBProject
    Set wbkModules = GetModuleWorkbook
    If wbkModules Is Nothing Then
        MsgBox "Can't open module workbook!"
        Exit Sub
    End If
    Set ToProj = wbkModules.VBProject
    ' Get the VBA project
    ' Set pVBAProject = ThisWorkbook.VBProject
    Set FromProj = Application.Workbooks("Oz_main.xls").VBProject
    CopyVBAFiles FromProj, ToProj
End Sub
Sub importfiles()
    Dim wbkModules As Workbook
    Dim FromProj As VBIDE.VBProject, ToProj As VBIDE.VBProject
    Set wbkModules = GetModuleWorkbook
    If wbkModules Is Nothing Then
        MsgBox "Can't open module workbook!"
        Exit Sub
    End If
    Set FromProj = wbkModules.VBProject
    ' Get the VBA project
    ' Set pVBAProject = ThisWorkbook.VBProject
    Set ToProj = Application.Workbooks("Oz_main.xls").VBProject
    CopyVBAFiles FromProj, ToProj

End Sub

Sub CopyVBAFiles(FromProj As VBIDE.VBProject, ToProj As VBIDE.VBProject)
    Dim VBComp As VBComponent 'VBA module, form, etc...
    Dim vbTemp As VBComponent
    Dim strSavePath As String  'Path to save the exported files to
    Dim strSaveName As String
    Dim S As String

    strSavePath = ThisWorkbook.Path & "\_VBACode"

    ' If this folder doesn't exist, create it
    If Dir(strSavePath, vbDirectory) = "" Then
        MkDir strSavePath
    End If
    For Each VBComp In FromProj.VBComponents
        strSaveName = ExportVBComponent(VBComp, strSavePath, , True)
        If strSaveName <> "" Then
            If VBComponentExists(VBComp.Name, ToProj) Then
                If VBComp.Type = vbext_ct_Document Then
                    ' object module so don't try and delete, just replace code!
                    Set vbTemp = ToProj.VBComponents.Import(strSaveName)
                    With ToProj.VBComponents(VBComp.Name).CodeModule
                        .DeleteLines 1, .CountOfLines
                        S = vbTemp.CodeModule.Lines(1, vbTemp.CodeModule.CountOfLines)
                        .InsertLines 1, S
                    End With
                    ' remove temp object
                    ToProj.VBComponents.Remove vbTemp
                Else
                    ToProj.VBComponents.Remove ToProj.VBComponents(VBComp.Name)
                    DoEvents
                    ToProj.VBComponents.Import strSaveName
                End If
            Else
                ToProj.VBComponents.Import strSaveName
            End If
        End If
    Next VBComp
    MsgBox "VBA files have been exported to: " & strSavePath
End Sub
Public Function GetFileExtension(VBComp As VBIDE.VBComponent) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This returns the appropriate file extension based on the Type of
' the VBComponent.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Select Case VBComp.Type
        Case vbext_ct_ClassModule
            GetFileExtension = ".cls"
        Case vbext_ct_Document
            GetFileExtension = ".cls"
        Case vbext_ct_MSForm
            GetFileExtension = ".frm"
        Case vbext_ct_StdModule
            GetFileExtension = ".bas"
        Case Else
            GetFileExtension = ".bas"
    End Select
    
End Function
Public Function ExportVBComponent(VBComp As VBIDE.VBComponent, _
            FolderName As String, _
            Optional FileName As String, _
            Optional OverwriteExisting As Boolean = True) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This function exports the code module of a VBComponent to a text
' file. If FileName is missing, the code will be exported to
' a file with the same name as the VBComponent followed by the
' appropriate extension.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Extension As String
Dim FName As String
Extension = GetFileExtension(VBComp:=VBComp)
If Trim(FileName) = vbNullString Then
    FName = VBComp.Name & Extension
Else
    FName = FileName
    If InStr(1, FName, ".", vbBinaryCompare) = 0 Then
        FName = FName & Extension
    End If
End If

If StrComp(Right(FolderName, 1), "\", vbBinaryCompare) = 0 Then
    FName = FolderName & FName
Else
    FName = FolderName & "\" & FName
End If

If Dir(FName, vbNormal + vbHidden + vbSystem) <> vbNullString Then
    If OverwriteExisting = True Then
        Kill FName
    Else
        ExportVBComponent = ""
        Exit Function
    End If
End If

VBComp.Export FileName:=FName
ExportVBComponent = FName

End Function
Public Function VBComponentExists(VBCompName As String, Optional VBProj As VBIDE.VBProject = Nothing) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This returns True or False indicating whether a VBComponent named
' VBCompName exists in the VBProject referenced by VBProj. If VBProj
' is omitted, the VBProject of the ActiveWorkbook is used.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim VBP As VBIDE.VBProject
    If VBProj Is Nothing Then
        Set VBP = ActiveWorkbook.VBProject
    Else
        Set VBP = VBProj
    End If
    On Error Resume Next
    VBComponentExists = CBool(Len(VBP.VBComponents(VBCompName).Name))

End Function
Function GetModuleWorkbook() As Workbook
    Const cstrWORKBOOK_PATH As String = "C:\Excel\"
    Const cstrWORKBOOK_NAME As String = "Excel_modules.xls"
    Dim wbk As Workbook
    On Error Resume Next
    Set wbk = Workbooks(cstrWORKBOOK_NAME)
    If wbk Is Nothing Then
        Set wbk = Workbooks.Open(cstrWORKBOOK_PATH & cstrWORKBOOK_NAME)
    End If
    Set GetModuleWorkbook = wbk
End Function

Open in new window

0
 

Author Comment

by:NerishaB
ID: 33565535
Thank you, I tried the code, but the Function GetModuleWorkBook returns nothing.  The path and name is correct, but the function still returns nothing.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33565552
Do you have that workbook open in a different instance of Excel?
0
 

Author Comment

by:NerishaB
ID: 33565658
Yes, I had opened the workbook in a different instance of Excel.  I got the export working properly, but not the import.  The files are exported to "C:\_VBA Files"  Now, in the import function, it reads GetModuleWorkbook, and will return nothing becasue it should not be looking for an opened Workbook, it needs to be looking for the files stored in "C:\_VBA files"
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33565684
I thought the whole point was to export the files to your temp workbook, so that you can make changes, then re-import them from there?
0
 

Author Comment

by:NerishaB
ID: 33565731
No, sorry, I wanted them to be exported to a temp location, "C:\_VBACode", then I can alter them in notepad, and therafter I want to export them back from the temp location ("C:\_VBACode")  Sorry for the misunderstanding.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33565809
I can't imagine why you would want to edit in Notepad rather than the VBE, but never mind.
Let's just clarify:
You want to export modules from the Excel_modules.xls workbook and import to the Oz_main workbook, but as two separate steps? Do you just want normal modules, or do you need worksheet/workbook code too?
0
 

Author Comment

by:NerishaB
ID: 33566083
Ok, I have completed the Import function.  The full code is displayed below.  My only issue now, is that the file "Oz_Excel.xls" needs to be opened in order for the export to happen.  Is there a way to have this file open automatically?
Option Explicit
Sub ExportFiles()
    Dim wbkModules As Workbook
    Dim FromProj As VBIDE.VBProject, ToProj As VBIDE.VBProject
    Set wbkModules = GetModuleWorkbook
    If wbkModules Is Nothing Then
        MsgBox "Can't open module workbook!"
        Exit Sub
    End If
    Set ToProj = wbkModules.VBProject
    ' Get the VBA project
    ' Set pVBAProject = ThisWorkbook.VBProject
    Set FromProj = Application.Workbooks("Oz_main.xls").VBProject
    ExportVBAFiles FromProj, ToProj
End Sub

Sub ImportModules()
    Application.ScreenUpdating = False
    Dim Filt As String, Title As String, FilterIndex As Integer, i As Integer, FileName
    Dim blnTest As Boolean
    Dim FSO As Object, Folder As Object, File As Object
    Dim TestXLS As Workbook
     
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set Folder = FSO.GetFolder(Application.StartupPath)
     
    For Each File In Folder.Files
        If UCase(File.Name) = "Test.XLS" Then
            If WorkbookIsOpen(File.Name) Then
                Set TestXLS = Application.Workbooks(File.Name)
            Else
                Set TestXLS = Application.Workbooks.Open(File.Path)
            End If
            blnTest = True
            Exit For
        End If
    Next
     
    If blnTest = True Then
        If MsgBox("Test.xls already exists." & vbCrLf & vbCrLf & _
        "Would you like to add modules to it?", vbYesNo, _
        "Test.xls Exists") = vbNo Then GoTo ExitHere
    End If
     
    If blnTest = False Then
        Set TestXLS = Application.Workbooks.Add
        TestXLS.SaveAs (Application.StartupPath & "\Test.xls")
        Windows("Test.xls").Visible = True
         
        If MsgBox("Test.xls created." & vbCrLf & vbCrLf & "Would you like to import modules?", _
        vbYesNo, "Test.xls Created") = vbNo Then GoTo ExitHere
    End If
     
    Filt = "All Files (*.*),*.*," & _
    "Basic Files (*.bas),*.bas," & _
    "Class Files (*.cls),*.cls," & _
    "Form Files (*.frm),*.frm,"

    FilterIndex = 5
    Title = "Select a File to Import"
    FileName = Application.GetOpenFilename(FileFilter:=Filt, FilterIndex:=FilterIndex, _
    Title:=Title, MultiSelect:=True)
     
    If TypeName(FileName) = "Boolean" Then GoTo ExitHere

    For i = LBound(FileName) To UBound(FileName)
        TestXLS.VBProject.VBComponents.Import (FileName(i))
    Next
     
ExitHere:
    TestXLS.Save
    Set TestXLS = Nothing
    Set FSO = Nothing
    Set Folder = Nothing
    Set File = Nothing
    Application.ScreenUpdating = True
End Sub
Private Function WorkbookIsOpen(wbName) As Boolean
     'Returns TRUE if the workbook is open
    Dim wb As Workbook
    On Error Resume Next
    Set wb = Application.Workbooks(wbName)
    If Err = 0 Then WorkbookIsOpen = True Else WorkbookIsOpen = False
End Function
 

Sub ExportVBAFiles(FromProj As VBIDE.VBProject, ToProj As VBIDE.VBProject)
    Dim VBComp As VBComponent 'VBA module, form, etc...
    Dim strSavePath As String  'Path to save the exported files to
    Dim strSaveName As String
    Dim S As String

    strSavePath = ThisWorkbook.Path & "\_VBACode"

    ' If this folder doesn't exist, create it
  If Dir(strSavePath, vbDirectory) = "" Then
    MkDir strSavePath
  End If
  
  ' Get the VBA project
  Set FromProj = Application.Workbooks("Oz_main.xls").VBProject
    
  ' Loop through all the components (modules, forms, etc) in the VBA project
  For Each VBComp In FromProj.VBComponents
    Select Case VBComp.Type
    Case vbext_ct_StdModule
      VBComp.Export strSavePath & "\" & VBComp.Name & ".bas"
    Case vbext_ct_Document, vbext_ct_ClassModule
      ' ThisDocument and class modules
      VBComp.Export strSavePath & "\" & VBComp.Name & ".cls"
    Case vbext_ct_MSForm
      VBComp.Export strSavePath & "\" & VBComp.Name & ".frm"
    Case Else
      VBComp.Export strSavePath & "\" & VBComp.Name
    End Select
  Next
    MsgBox "VBA files have been exported to: " & strSavePath
End Sub

Public Function GetFileExtension(VBComp As VBIDE.VBComponent) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This returns the appropriate file extension based on the Type of
' the VBComponent.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Select Case VBComp.Type
        Case vbext_ct_ClassModule
            GetFileExtension = ".cls"
        Case vbext_ct_Document
            GetFileExtension = ".cls"
        Case vbext_ct_MSForm
            GetFileExtension = ".frm"
        Case vbext_ct_StdModule
            GetFileExtension = ".bas"
        Case Else
            GetFileExtension = ".bas"
    End Select
    
End Function
Public Function ExportVBComponent(VBComp As VBIDE.VBComponent, _
            FolderName As String, _
            Optional FileName As String, _
            Optional OverwriteExisting As Boolean = True) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This function exports the code module of a VBComponent to a text
' file. If FileName is missing, the code will be exported to
' a file with the same name as the VBComponent followed by the
' appropriate extension.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Extension As String
Dim FName As String
Extension = GetFileExtension(VBComp:=VBComp)
If Trim(FileName) = vbNullString Then
    FName = VBComp.Name & Extension
Else
    FName = FileName
    If InStr(1, FName, ".", vbBinaryCompare) = 0 Then
        FName = FName & Extension
    End If
End If

If StrComp(Right(FolderName, 1), "\", vbBinaryCompare) = 0 Then
    FName = FolderName & FName
Else
    FName = FolderName & "\" & FName
End If

If Dir(FName, vbNormal + vbHidden + vbSystem) <> vbNullString Then
    If OverwriteExisting = True Then
        Kill FName
    Else
        ExportVBComponent = ""
        Exit Function
    End If
End If

VBComp.Export FileName:=FName
ExportVBComponent = FName

End Function
Public Function VBComponentExists(VBCompName As String, Optional VBProj As VBIDE.VBProject = Nothing) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This returns True or False indicating whether a VBComponent named
' VBCompName exists in the VBProject referenced by VBProj. If VBProj
' is omitted, the VBProject of the ActiveWorkbook is used.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim VBP As VBIDE.VBProject
    If VBProj Is Nothing Then
        Set VBP = ActiveWorkbook.VBProject
    Else
        Set VBP = VBProj
    End If
    On Error Resume Next
    VBComponentExists = CBool(Len(VBP.VBComponents(VBCompName).Name))

End Function
Function GetModuleWorkbook() As Workbook
    Const cstrWORKBOOK_PATH As String = "C:\Netserver\Oztech_dev_oz\Oz_excel"
    Const cstrWORKBOOK_NAME As String = "oz_main.xls"
    Const cstrImport_Path As String = "C:\_VBACode"
    Const cstrImport_Name As String = ""
    Dim wbk As Workbook
    On Error Resume Next
    Set wbk = Workbooks(cstrWORKBOOK_NAME)
    If wbk Is Nothing Then
        Set wbk = Workbooks.Open(cstrWORKBOOK_PATH & cstrWORKBOOK_NAME)
    End If
    Set GetModuleWorkbook = wbk
End Function

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33566157
Change line 101 from this:
Set FromProj = Application.Workbooks("Oz_main.xls").VBProject
to this:
Set FromProj = GetModuleWorkbook().VBProject


0
 

Author Comment

by:NerishaB
ID: 33566250
I tried that.  The function GetModuleWorkbook returns nothing.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33566366
You need a backslash on the end of the path to the workbook:
Const cstrWORKBOOK_PATH As String = "C:\Netserver\Oztech_dev_oz\Oz_excel\"
0
 

Author Comment

by:NerishaB
ID: 33566628
Thank you.  The workbook is password protected.  Is there a way for me to unprotect this workbook in the code?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33566656
What kind of protection? To open the file, to alter the file, or VBA project? (If the latter, no.)
0
 

Author Comment

by:NerishaB
ID: 33566754
There is a password to open the VBA project.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 33566778
Then, as I said, no. Sendkeys is not reliable.
0
 

Author Closing Comment

by:NerishaB
ID: 33566866
Thanks for all your help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

722 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