Solved

Update Spreadsheet From Remote Spreadsheet

Posted on 2014-01-26
7
471 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
  • 4
  • 3
7 Comments
 
LVL 80

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
 

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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 80

Accepted Solution

by:
byundt earned 500 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 80

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
 

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
 

Author Closing Comment

by:tracyms
ID: 39810930
Thank byundt!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

A short article about problems I had with the new location API and permissions in Marshmallow
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now