VBA - issues with pulling source data from password protected workbook

[jwarren73]

I'm using VBA to pull a large amount of data from a closed workbook that's password protected. While the code that I've put in works smoothly, there are 2 annoying issues that remain that I'd like to get rid of, which I suspect may be related.
1) I have to enter the password twice - once when selecting the workbook to copy data from, and again after I select which sheet in the workbook to pull data from.
2) I also get a pop-up warning that formulas can not be copied, and that only values will be, despite using a pastespecial with values option.

I know that #2 is tied to the source workbook being opened as a new instance of Excel rather than opening it within the same instance, and I suspect that #1 may be due to the same.

I have limited VBA experience, but have been figuring it out as I go - how can I force Excel to open the new workbook within the same instance, instead of creating a new instance? Also, if the double password issue isn't tied to this, any suggestions on how to clear that up? I've done numerous searches on this site as well as general Google searches without having any luck finding code that directly relates to my issues, any help would be appreciated.

I'm using Windows 7 and Excel 2010.

 

[AlphaFrog]

Can you post your code?

 

[jwarren73]

Can you post your code?

Here's the code from the 2 relevant modules and userform:

**************************Module 1:

'Global Variables
Public gstrFileName As String 'Holds the source filename
Public gstrDate As String 'Holds the requested tab name/date
Public glngDay As String 'Holds the day in the pay period

Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Public Declare Function CloseClipboard Lib "user32" () As Long
  
Public Function ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Function

*************************Module 4:

    ChDir "C:\"

    gstrFileName = Application.GetOpenFilename(FileFilter:="microsoft excel files (*.xls*), *.xls*", Title:="Select Pay Period Data Source File", MultiSelect:=False)
    
    If gstrFileName <> "" And gstrFileName <> "False" Then
        Application.ScreenUpdating = False
        Dim app As New Excel.Application
        Dim book As Excel.Workbook
        Dim lngCount As Long
        
        Set book = app.Workbooks.Add(gstrFileName)
        For lngCount = 1 To book.Sheets.Count
            frmDataImport.lstPPs.AddItem book.Sheets(lngCount).Name
        Next lngCount
        
        book.Close SaveChanges:=False
        app.Quit
        Set app = Nothing
        Application.ScreenUpdating = False
        frmDataImport.Show 1
    End If


********************frmDataImport

Private Sub cmdCancel_Click()
    Unload frmDataImport
End Sub

Private Sub cmdOK_Click()
    Dim app As New Excel.Application
    Dim book As Excel.Workbook
    Dim lngRow As Long
    Dim lngRow2 As Long
    Dim lngCol As Long
    Dim lngLoop As Long
    Dim PPSourceDataSheet As String
    Dim PT As PivotTable
    Dim ws As Worksheet
    Dim SourceData As Worksheet
    Dim DestData As Worksheet
    

    If lstPPs.ListIndex = -1 Then lstPPs.ListIndex = 0
    
    frmDataImport.Hide
    Application.Cursor = xlWait
    Application.ScreenUpdating = False
    

    'Access the new pay period data
    Set book = app.Workbooks.Add(gstrFileName)
    PPSourceDataSheet = lstPPs.List(lstPPs.ListIndex)

    
    Set SourceData = book.Sheets(PPSourceDataSheet)
    Set DestData = ThisWorkbook.Sheets(PPSourceDataSheet)
    'Copy from new pay period data and write to existing sheet
    

    
With SourceData.Cells.Copy
DestData.Range("A1").PasteSpecial Paste:=xlValues


End With
Application.CutCopyMode = False


' Empty data from clipboard
Call ClearClipboard
          
    book.Close SaveChanges:=False
    app.Quit
    Set app = Nothing

    Unload frmDataImport


Private Sub lstPPs_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    cmdOK_Click
End Sub

Private Sub UserForm_Click()

End Sub

 

[AlphaFrog]

This opens the workbook in the same instance of Excel.
It leaves the workbook open for later use when you copy the sheet values. Then closes it after the copy.
Change the workbook password to suit.

[COLOR=green]'*************************Module 4:[/COLOR]
    
    ChDir "C:\"
    
    gstrFileName = Application.GetOpenFilename(FileFilter:="microsoft excel files (*.xls*), *.xls*", Title:="Select Pay Period Data Source File", MultiSelect:=False)
    
    [COLOR=darkblue]If[/COLOR] gstrFileName <> "" And gstrFileName <> "False" [COLOR=darkblue]Then[/COLOR]
        Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
        [COLOR=darkblue]With[/COLOR] Workbooks.Open(gstrFileName, Password:=[COLOR=#ff0000]"Secret"[/COLOR])
            [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] .Worksheets
                frmDataImport.lstPPs.AddItem ws.Name
            [COLOR=darkblue]Next[/COLOR] ws
            [COLOR=green]'.Close SaveChanges:=False[/COLOR]
            gstrFileName = .Name
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        ThisWorkbook.Activate
        Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
        frmDataImport.Show 1
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdOK_Click()
    [COLOR=green]'Dim lngRow As Long[/COLOR]
    [COLOR=green]'Dim lngRow2 As Long[/COLOR]
    [COLOR=green]'Dim lngCol As Long[/COLOR]
    [COLOR=green]'Dim lngLoop As Long[/COLOR]
    Dim PPSourceDataSheet [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=green]'Dim PT As PivotTable[/COLOR]
    [COLOR=green]'[COLOR=darkblue]Dim[/COLOR] ws As Worksheet[/COLOR]
    Dim SourceData [COLOR=darkblue]As[/COLOR] Worksheet
    Dim DestData [COLOR=darkblue]As[/COLOR] Worksheet
    
    [COLOR=darkblue]If[/COLOR] lstPPs.ListIndex = -1 [COLOR=darkblue]Then[/COLOR] lstPPs.ListIndex = 0
    
    frmDataImport.Hide
    Application.Cursor = xlWait
    [COLOR=green]'Application.ScreenUpdating = False[/COLOR]
    
    [COLOR=green]'Access the new pay period data[/COLOR]
    [COLOR=darkblue]With[/COLOR] Workbooks(gstrFileName)
        PPSourceDataSheet = lstPPs.List(lstPPs.ListIndex)
        [COLOR=darkblue]Set[/COLOR] SourceData = .Sheets(PPSourceDataSheet)
        [COLOR=darkblue]Set[/COLOR] DestData = ThisWorkbook.Sheets(PPSourceDataSheet)
        [COLOR=green]'Copy from new pay period data and write to existing sheet[/COLOR]
        [COLOR=darkblue]With[/COLOR] SourceData.UsedRange
            DestData.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        
        .Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    Unload frmDataImport

 

[jwarren73]

Perfect, that cleared up both issues - thank you very much. The only change that I had to make was removing the password definition, as the password will be different on different source files.