Solved

Update Spreadsheet From Remote Spreadsheet

Posted on 2014-01-26
7
502 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 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 81

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 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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

773 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