asked on

Excel 2007 vb within one workbook to check if another workbook is open and either open or continue

Hello Experts!

Back with another question.

I am using Excel 2007. Within a specific workbook I have a macro that needs to:
1st) check if another workbook (say wbk2) is open:

a)If it is continue on to find the last row of input from each of specific series of worksheets in wbk1 and paste the values to wbk2 on specific series of sheets

b)if not open wbk2 and then copy from wbk1 to wbk2 as in a) above.

Following is what I have. The if then statement is where I have a problem.

Sub SaveDataInput()
    ' Add This Shifts LVL Totals for Day as of the End of Shift to LVL Total for Day Export worksheet
    '
    ' Keyboard Shortcut: Ctrl+Shift+Z
    Dim wbk As Workbook
    Dim inputRange As Range, outputRange As Range
    
    strSaveToFile = "U:\DATA\XYZ Co\Workpapers\Shift Details Data.xlsx"
  
' Begin Save info from input file LVL Total For Day Export Temp sheet to Data file LVL Total For Day Export sheet
    LastRowInput = Sheets("LVL Total For Day Export Temp").Range("A" & Rows.Count).End(xlUp).Row
    
    Set inputRange = Sheets("LVL Total For Day Export Temp").Range("A2:AC" & LastRowInput)
    
' NEED IF THEN to check if wbk being copied to is open, if yes continue copy and paste value, if no open then continue copy and paste
    Set wbk = Workbooks.Open(strSaveToFile)
    LastRowOutput = Sheets("LVL Total For Day Export").Range("A" & Rows.Count).End(xlUp).Row + 1
    Set outputRange = Sheets("LVL Total For Day Export").Range("A" & LastRowOutput)
    
    inputRange.Copy
    
    outputRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False

' End Save info from input file LVL Total For Day Export Temp sheet to Data file LVL Total For Day Export sheet
  
' Begin Save info from input file LVL Total For Day Export Temp sheet to Data file LVL Total For Day Export sheet
    LastRowInput = Sheets("LVL Shift Only Export Temp").Range("A" & Rows.Count).End(xlUp).Row
    
    Set inputRange = Sheets("LVL Shift Only Export Temp").Range("A2:AC" & LastRowInput)
    
    Set wbk = Workbooks.Open(strSaveToFile)
    LastRowOutput = Sheets("LVL Shift Only Export").Range("A" & Rows.Count).End(xlUp).Row + 1
    Set outputRange = Sheets("LVL Shift Only Export").Range("A" & LastRowOutput)
    
    inputRange.Copy
    
    outputRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False

' End Save info from input file LVL Total For Day Export Temp sheet to Data file LVL Total For Day Export sheet
    
    
    
' Save updated data file then close
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
    
    'This hides the New Pay Period Input worksheet again after saving New Pay Period date
'    Sheets(S & "LVL Total For Day Export Temp").Visible = 2



End Sub

Open in new window

SiddharthRout

Here is a sample code on how to check if a Workbook is open or not.

Sub Sample()
    Dim wBook As Workbook
    Dim strFile As String
    strFile = "C:\MyFile.xlsx"
    
    On Error Resume Next
    Set wBook = Workbooks(Dir(strFile))
    On Error GoTo 0
    
    If wBook Is Nothing Then
        MsgBox "not open"
    Else
        MsgBox " open"
    End If
End Sub

Open in new window

Sid

wlwebb

ASKER

Sid thanks,

Since I will be copying a range from multiple sheets from wbk1 to multiple sheets on wbk2 is there a line of code to switch back and forth to which file is my "active" file.

Example
1st Copy and Paste
wbk1
Copy sheet1 a2:ac3

wbk2
Paste values sheet xyz a(last row)

2nd Copy and Paste
wbk1
Copy sheet7 a12:ac23

wbk2
Paste values sheet abc a(last row)

SiddharthRout

You don't need to switch between sheets or workbooks. You can do it in one line of code

For example if you want to copy from Sheet1 to Sheet2 then you can use this

Sheets("Sheet1").Range("A1").Copy Sheets("Sheet2").Range("A1")

If you want to copy from Sheet1 in workbook1 to Sheet2 in workbook2 then the above code becomes

Wb1.Sheets("Sheet1").Range("A1").Copy Wb2.Sheets("Sheet2").Range("A1")

Where Wb1 and Wb2 are the respective workbook objects.

Sid

wlwebb

ASKER

Thanks I will give that a try. We Newbies always try to make things harder than they are.

SiddharthRout

Let me know if you get stuck ;)

Sid

wlwebb

ASKER

Errors out. Note I am having the macro find the last input line before copy and pasting.

Here is the code where it errors out.

Stops on this line:
LastRowInput = strCopyFromFile.Sheets("LVL Total For Day Export Temp").Range("A" & Rows.Count).End(xlUp).Row

Sub SaveDataInput()
    Dim wbk As Workbook
    Dim strCopyFromFile As String
    Dim strSaveToFile As String
    Dim inputRange As Range, outputRange As Range
    
    strCopyFromFile = "Clerk Shift Report.xlsm"
    strSaveToFile = "U:\DATA\Workpapers\Shift Details Data.xlsx"

' Test if save to workbook is open
 On Error Resume Next
 Set wbk = Workbooks(Dir(strSaveToFile))
 On Error GoTo 0
 
    If wbk Is Nothing Then
        Set wbk = Workbooks.Open(strSaveToFile)
        ' MsgBox "not open"
    Else
        ' MsgBox " Open"
    End If
' End Test if save to workbook is open

' Begin Save info 
    LastRowInput = strCopyFromFile.Sheets("LVL Total For Day Export Temp").Range("A" & Rows.Count).End(xlUp).Row
    
    Set inputRange = strCopyFromFile.Sheets("LVL Total For Day Export Temp").Range("A2:AC" & LastRowInput)

    LastRowOutput = strSaveToFile.Sheets("LVL Total For Day Export").Range("A" & Rows.Count).End(xlUp).Row + 1
    Set outputRange = strSaveToFile.Sheets("LVL Total For Day Export").Range("A" & LastRowOutput)
    
    inputRange.Copy
    
    outputRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False

' End Save Info

End Sub

Open in new window

SiddharthRout

Quick question. Are you running this code from Clerk Shift Report.xlsm?

Sid

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wlwebb

ASKER

Yes I am running it from Clerk Shift Report.xlsm

wlwebb

ASKER

PERFECTION!!! But, one follow on question which I referred to in my original question. { I am going to award half the points for the first part of the solution and then the other half for the follow on.

It is regarding copying from multiple sheets and pasting to multiple sheets.

Do I just repeat the whole "Begin Save Info" through the "Application.CutCopyMode=False"

OR

Do just add a "Set ws2, Set ws3 etc, Set ws4 to identify the other copy from sheets and add Set statements for all the Copy to sheets?

SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wlwebb

ASKER

Sid is a master. Thanks again for all your help