VBA for multiple excel file

[vishu4v]

Hello,

I have a excel file (workflowtracker.xls) with 2 sheets. Sheet 1 - user input and sheet 2 (dashboard) using values from sheet1. my dashboard in sheet2 in the same excel file works as per my requirement. Sheet 1 has all the user input data and sheet 2(dashboard) has a summary of data or you can say a list of delayed deliveries, WIP etc.

Now I want to make the dashboard working from a separate excel file (xyz.xls) as keeping both the sheets in the same file is a security risk.

Can anyone help me with the code. my sheet 2 should be in a separate excel file. How to call the the first excel file.

Below is my code which works when both the sheet are in the same excel file.


Option Explicit

Dim TempVal As String

Private Sub Worksheet_Activate()

Application.DisplayAlerts = False
TempVal = Range("G5").Value
Range("G5").Value = "Wait Refreshing Data"
Application.ScreenUpdating = False
If ActiveSheet.AutoFilterMode = False Then
Range("A5:G65536").Select
Selection.AutoFilter
Else
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
End If

Range("A6").FormulaR1C1 = _
"=IF(OR('Daily Workflow Tracker'!RC[20]>=3,'Daily Workflow Tracker'!RC[22]>0),'Daily Workflow Tracker'!RC,"""")"
Range("B6").FormulaR1C1 = "=IF(RC[-1]<>"""",'Daily Workflow Tracker'!RC[5],"""")"
Range("C6").FormulaR1C1 = "=IF(RC[-2]<>"""",'Daily Workflow Tracker'!RC[8],"""")"
Range("D6").FormulaR1C1 = "=IF(RC[-3]<>"""",'Daily Workflow Tracker'!RC[10],"""")"
Range("E6").FormulaR1C1 = "=IF(RC[-4]<>"""",'Daily Workflow Tracker'!RC[16],"""")"
Range("F6").FormulaR1C1 = "=IF(RC[-5]<>"""",'Daily Workflow Tracker'!RC[17],"""")"

Please help...