Solved

Excel VBA - Need to split export/import function

Posted on 2010-08-31
24
345 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
  • 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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

12 Experts available now in Live!

Get 1:1 Help Now