[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Update Spreadsheet From Remote Spreadsheet

Posted on 2014-01-26
7
Medium Priority
?
536 Views
Last Modified: 2014-01-26
Hello Experts.

I'd like to create a button that will update a worksheet in my workbook with a worksheet from a remote workbook (different location).

I'd like to have this happen:

- User opens workbook
- Clicks button that says "Update Master"
- The "Master" worksheet columns B through I are deleted and replaced with columns A through H from the remote worksheet

Some things to note:

I currently update this manually and have to put the sheet in design mode (click on Developer Tab, then Design Mode Tab) because I have some vb code that sorts the Master sheet when a value is changed on another sheet in the workbook.

Once I replace columns B through I, I click on the Design Mode Tab again to take sheet out of Design Mode to make it useable again before sending to users.

I have a formula in column A which concatenates columns B and C, which is why I only update B through I:

=IF(OR(B2="A Continuation of Previous Course",B2="A New Course"),B2,C2&" "&" "&B2)

Spreadsheet is protected (no password).

"Master" worksheet is hidden.

I can schedule the remote spreadsheet to update daily with the data I need in these columns (B through I). This data is exported from our database into an excel spreadsheet but it starts at Column A, thus making the new data in Columns A through H.


I'd like to automate this by allowing the user to click a button and it updates so they'll have the most current information in the spreadsheet - thus eliminating my manual process.

Also would like to notify user when process is complete so they know to begin using the sheet.


Thank you!
0
Comment
Question by:tracyms
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39810778
I believe you can avoid the need to enter Design mode by turning events off while the updating occurs.

You'll need to modify the code below for the actual path, filename and sheet name of the remote file. There should just be a flicker of the screen while the macro runs, as the remote workbook is opened, its data is copied and pasted, and then the remote workbook is closed.
Sub UpdateMaster()
Dim flPath As String, flName As String, shName As String
Dim wb As Workbook
Dim celHome As Range, rgDest As Range, rgSource As Range
Application.ScreenUpdating = False
Set celHome = ActiveCell
flPath = "X:\VBA\Sample '13\"   'Path to remote workbook
flName = "Book2.xlsx"           'Name of remote workbook
shName = "Sheet1"               'Name of sheet in remote workbook
With Worksheets("Master")
    Set rgDest = Intersect(.UsedRange, .Range("B:I"))
    .Unprotect
    .Protect UserInterfaceOnly:=True
End With
Set wb = Workbooks.Open(flPath & flName)
With wb.Worksheets(shName)
    Set rgSource = Intersect(.UsedRange, .Range("A:H"))
End With
Application.EnableEvents = False
rgDest.ClearContents
rgSource.Copy
rgDest.Cells(1, 1).PasteSpecial xlPasteValues
wb.Close SaveChanges:=False
Application.Goto celHome
MsgBox "Macro has completed the updates"
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:tracyms
ID: 39810871
Wow - really cool. A couple of things:

I had to enable events at the end of your code - Application.EnableEvents = True - because some of my vba code stopped firing. I also added the code from the Master sheet to re-sort the data. The only thing is that the rows are highlighted on main sheet when all is done. I'm guessing it's because "Columns("B:I").Select" is not referencing the Master sheet but I don't know how to fix that - can you help? Thanks!

Private Sub CommandButton4_Click()
Dim flPath As String, flName As String, shName As String
Dim wb As Workbook
Dim celHome As Range, rgDest As Range, rgSource As Range
Application.ScreenUpdating = False
Set celHome = ActiveCell
flPath = "C:\Users\myName\Desktop\"   'Path to remote workbook
flName = "MasterSheet.xlsx"           'Name of remote workbook
shName = "MasterUpdate"               'Name of sheet in remote workbook
With Worksheets("Master")
    Set rgDest = Intersect(.UsedRange, .Range("B:I"))
    .Unprotect
    .Protect UserInterfaceOnly:=True
End With
Set wb = Workbooks.Open(flPath & flName)
With wb.Worksheets(shName)
    Set rgSource = Intersect(.UsedRange, .Range("A:H"))
End With
Application.EnableEvents = False
rgDest.ClearContents
rgSource.Copy
rgDest.Cells(1, 1).PasteSpecial xlPasteValues
wb.Close SaveChanges:=False
Application.Goto celHome
On Error Resume Next

    If Not Intersect(Target, Range("D:D")) Is Nothing Then
        Columns("B:I").Select
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add Key:=Range("D:D" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add Key:=Range("C:C" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Master").Sort
        .SetRange Range("B:I")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlSortColumns
        .SortMethod = xlPinYin
        .Apply
    End With
    
    End If
MsgBox "Macro has completed the updates"
Application.EnableEvents = True

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:tracyms
ID: 39810880
Sorry - one more thing. When I click the button it comes with message about large amount of data on the clip board and asks it want to save for another application. I click no and your code still works but can I stop this from showing up? Maybe not since it's a Microsoft Windows message...
0
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!

 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 39810906
My goof on omitting the EnableEvents =True.

I had a statement Application.GoTo celHome which was supposed to return the cursor to its starting point. That statement needs to be at the end of the macro. You also had an If block that looks like it was lifted from a Worksheet_Change event sub. It won't work in your new macro because Target isn't defined, and should be deleted.
If Not Intersect(Target, Range("D:D")) Is Nothing Then

Open in new window



I reorganized your code using a worksheet variable pointing to the Master worksheet.
Private Sub CommandButton4_Click()
Dim flPath As String, flName As String, shName As String
Dim wb As Workbook
Dim wsMaster As Worksheet
Dim celHome As Range, rgDest As Range, rgSource As Range
Application.ScreenUpdating = False
Set celHome = ActiveCell
flPath = "C:\Users\myName\Desktop\"   'Path to remote workbook
flName = "MasterSheet.xlsx"           'Name of remote workbook
shName = "MasterUpdate"               'Name of sheet in remote workbook
Set wsMaster = Worksheets("Master")
With wsMaster
    Set rgDest = Intersect(.UsedRange, .Range("B:I"))
    .Unprotect
    .Protect UserInterfaceOnly:=True
End With
Set wb = Workbooks.Open(flPath & flName)
With wb.Worksheets(shName)
    Set rgSource = Intersect(.UsedRange, .Range("A:H"))
End With

Application.EnableEvents = False
rgDest.ClearContents
rgSource.Copy
wsMaster.Range("B1").PasteSpecial xlPasteValues
wb.Close SaveChanges:=False

On Error Resume Next
With wsMaster.Sort
    .SortFields.Clear
    .SortFields.Add Key:=wsMaster.Range("D:D"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=wsMaster.Range("C:C"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
    .SetRange wsMaster.Range("B:I")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlSortColumns
    .SortMethod = xlPinYin
    .Apply
End With

Application.Goto celHome
Application.EnableEvents = True
MsgBox "Macro has completed the updates"
End Sub

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 39810913
Regarding the clipboard warning message, you might try turning off alert messages while the macro runs:
Application.DisplayAlerts = False
'Code goes here
Application.DisplayAlerts = True

Open in new window

0
 
LVL 1

Author Comment

by:tracyms
ID: 39810920
I figured it out and updated the code but will try yours. Here's mine (with Application.DisplayAlerts = False/True) to suppress clipboard message).


Private Sub CommandButton4_Click()
Dim flPath As String, flName As String, shName As String
Dim wb As Workbook
Dim celHome As Range, rgDest As Range, rgSource As Range
Application.ScreenUpdating = False
Set celHome = ActiveCell
flPath = "C:\Users\myName\Desktop\"   'Path to remote workbook
flName = "MasterSheet.xlsx"           'Name of remote workbook
shName = "MasterUpdate"               'Name of sheet in remote workbook
With Worksheets("Master")
    Set rgDest = Intersect(.UsedRange, .Range("B:I"))
    .Unprotect
    .Protect UserInterfaceOnly:=True
End With
Set wb = Workbooks.Open(flPath & flName)
With wb.Worksheets(shName)
    Set rgSource = Intersect(.UsedRange, .Range("A:H"))
End With
Application.EnableEvents = False
rgDest.ClearContents
Application.DisplayAlerts = False
rgSource.Copy
rgDest.Cells(1, 1).PasteSpecial xlPasteValues
wb.Close SaveChanges:=False
Application.Goto celHome
On Error Resume Next

  
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add Key:=Range("D:D" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add Key:=Range("C:C" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Master").Sort
        .SetRange Range("B:I")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlSortColumns
        .SortMethod = xlPinYin
        .Apply
    End With
    
   
MsgBox "Master course list update is complete."
Application.DisplayAlerts = True
Application.EnableEvents = True
Range("C3").Activate

End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:tracyms
ID: 39810930
Thank byundt!
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Introduction to Processes

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question