• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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

0
wlwebb
Asked:
wlwebb
  • 6
  • 6
2 Solutions
 
SiddharthRoutCommented:
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
0
 
wlwebbAuthor Commented:
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)

0
 
SiddharthRoutCommented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
wlwebbAuthor Commented:
Thanks I will give that a try. We  Newbies always try to make things harder than they are.
0
 
SiddharthRoutCommented:
Let me know if you get stuck ;)

Sid
0
 
wlwebbAuthor Commented:
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

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

Sid
0
 
SiddharthRoutCommented:
If yes, then is this what you are trying?

Sub SaveDataInput()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim strCopyFromFile As String, strSaveToFile As String
    Dim inputRange As Range, outputRange As Range
    Dim LastRowInput As Long
    
    strCopyFromFile = "Clerk Shift Report.xlsm"
    strSaveToFile = "U:\DATA\Workpapers\Shift Details Data.xlsx"
    
    Set wb1 = ActiveWorkbook
    Set ws1 = wb1.Sheets("LVL Total For Day Export Temp")
    
    '~~> Test if save to workbook is open
    On Error Resume Next
    Set wb2 = Workbooks(Dir(strSaveToFile))
    Set ws2 = wb2.Sheets("LVL Total For Day Export Temp")
    On Error GoTo 0
    
    If wb2 Is Nothing Then
        Set wb2 = Workbooks.Open(strSaveToFile)
        Set ws2 = wb2.Sheets("LVL Total For Day Export Temp")
    End If

    '~~> Begin Save info
    LastRowInput = ws1.Range("A" & Rows.Count).End(xlUp).Row
    LastRowOutput = ws2.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    Set inputRange = ws1.Range("A2:AC" & LastRowInput)
    Set outputRange = ws2.Range("A" & LastRowOutput)
    
    inputRange.Copy
    
    outputRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    
    Application.CutCopyMode = False
End Sub

Open in new window


Sid
0
 
wlwebbAuthor Commented:
Yes I am running it from Clerk Shift Report.xlsm
0
 
wlwebbAuthor Commented:
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?
0
 
SiddharthRoutCommented:
>>>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?

Yes. :)

Sid
0
 
wlwebbAuthor Commented:
Sid is a master.  Thanks again for all your help
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now