Open File Dialog box - VBA

[rilzniak]

I'm losing my mind and need some help. I might just be missing something having stared at my screen for way too long today.

I have some code that puts a formula in a cell which references a closed workbook, spits out the result, and then pastes it as a value. What I can't figure it is:

1) Why the Open File Dialog box pops up every time I run the code. I've specified where to get the data from (at least I believe I have);
2) Why the VLOOKUP trips the code.

Can someone have a quick look and see if there's something glaring that I've missed? Or perhaps offer suggestions to my expanding code mess?

Option Explicit

Public i As Integer, j As Byte, m As Byte, MyRowCount As Long, IIRWProdCol As Byte, IIRWCounted As Byte, IIRWCalculated As Byte, LastColumn As Integer, StartRow As Integer, EndRow As Integer
Public Path1 As String, Path2 As String, Path3 As String, Path4 As String
Public SheetName As String, ArrayBegCol As String, ArrayEndCol As String, LookUpPeriod As String, Source As String, Filename As String
Public ProdColLetter As String, CountedCol As String, CalculatedCol As String
Public MinutesElapsed As Integer
Public StartDate As Date, EndDate As Date
Public StartTime As Double

Public Sub IIRWce()
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

StartTime = Timer

ActiveWorkbook.Sheets("Inventory").Activate
MyRowCount = Application.CountA(Range("A:A"))
LastColumn = ActiveWorkbook.Sheets("Inventory").Cells(2, Columns.Count).End(xlToLeft).Column
StartRow = 26
EndRow = 26

For i = StartRow To EndRow
    
    Path1 = Range("A" & i).Value
    Path2 = Range("B" & i).Value
    Path3 = Range("C" & i).Value
    Path4 = Range("D" & i).Value
    Filename = Range("E" & i).Value
    Source = Range("F" & i).Value
    LookUpPeriod = Range("H1")
    StartDate = Range("I1")
    ArrayBegCol = Range("J" & i).Value
    ArrayEndCol = Range("K" & i).Value
    SheetName = Range("L" & i).Value
    EndDate = Range("L1")

    IIRWProdCol = 2
    IIRWCounted = 10
    IIRWCalculated = 11
    ProdColLetter = Chr(WorksheetFunction.Match("Produced", Sheets("Inventory").Range("2:2"), 0) + 64)
    CountedCol = WorksheetFunction.Match("Counted", Sheets("Inventory").Range("2:2"), 0)
    CalculatedCol = WorksheetFunction.Match("Calculated", Sheets("Inventory").Range("2:2"), 0)
           
    If LookUpPeriod = "Date" Then
            
            Call Production_M_Date
            Call Inventory_M_Date

        Else

            Call Production_M
            Call Inventory_M
        
    End If


Cells.Replace "", 0

Next i


Application.Calculation = xlCalculationAutomatic

Range("M" & StartRow & ":V" & EndRow + 1).Copy
Range("M" & StartRow & ":V" & EndRow + 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("H1").Activate

Cells.Replace "#N/A", 0, xlWhole

'Determine how many seconds code took to run
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")

'Notify user in seconds
MsgBox "Run twice, the first time doesn't work!" & vbNewLine & "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

--------

Sub Production_M_Date()

    Range(ProdColLetter & i).Value = "=VLOOKUP(I1,'" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!" & ArrayBegCol & ":" & ArrayEndCol & "," & IIRWProdCol & ",FALSE)"

End Sub

--------

Sub Inventory_M_Date()
   
    For j = 13 To 21 'loop from column M to V on Inventory worksheet in IIRW file
        ActiveSheet.Cells(i, j).Value = "=IFERROR(VLOOKUP(I1,'" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!" & ArrayBegCol & ":" & ArrayEndCol & "," & j - 8 & ",FALSE),0)"
    Next j
    
End Sub

--------

Sub Production_M()
     
Range(ProdColLetter & i).Value = "=SUMPRODUCT(--('" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!R5C3:R370C3>=date(" & Year(StartDate) & "," & Month(StartDate) & "," _
    & Day(StartDate) & ")),--('" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!R5C3:R370C3<=date(" & Year(EndDate) & "," & Month(EndDate) & "," & Day(EndDate) _
    & ")),('" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!R5C4:R370C4))"
    
End Sub

--------

Sub Inventory_M()

    For m = 13 To 20
        ActiveSheet.Cells(i, m).Value = "=SUMPRODUCT(--('" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!R5C3:R370C3>=date(" & Year(StartDate) & "," _
        & Month(StartDate) & "," & Day(StartDate) & ")),--('" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!R5C3:R370C3<=date(" & Year(EndDate) & "," _
        & Month(EndDate) & "," & Day(EndDate) & ")),('" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!R5C" & m - 9 & ":R370C" & m - 9 & "))"
    Next m
        
    ActiveSheet.Cells(i, CountedCol).Value = "=VLOOKUP(I1,'" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!" & ArrayBegCol & ":" & ArrayEndCol & "," & IIRWCounted & ",FALSE)"
    ActiveSheet.Cells(i, CalculatedCol).Value = "=VLOOKUP(I1,'" & Path1 & "\" & Path2 & "\" & Path3 & "\" & Path4 & "\[" & Filename & "]" & SheetName & "'!" & ArrayBegCol & ":" & ArrayEndCol & "," & IIRWCalculated & ",FALSE)"

End Sub

 

[James006]

Hello,

The macro you posted does not seem to open a workbook ...

 

[rilzniak]

The macro you posted does not seem to open a workbook ...

It does not. I'm trying to avoid opening/closing the workbook but am not having much luck.

 

[James006]

Sorry ...

Read your message too quickly ...

Whenever faced with this situation, would rely on the master of All : John Walkenbach

His VBA function is just perfect ...!!!

http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

Hope this will help

 

[rilzniak]

Thanks!

 

[James006]

You are welcome ...