+ Reply to Thread
Results 1 to 5 of 5

Import data from workbook into Active Sheet, and Allow manipulation of WB around MsgBox

Hybrid View

  1. #1
    Registered User
    Join Date
    07-22-2013
    Location
    Norfolk, VA
    MS-Off Ver
    Excel 2010
    Posts
    13

    Question Import data from workbook into Active Sheet, and Allow manipulation of WB around MsgBox

    I am attempting to import data out of a weekly excel sheet, into the master workbook. The goal is to have 3 things ha[[en when the user clicks the button:

    1 - MsgBox pops up saying "Be sure to select the correct destination worksheet before clicking OK."
    1.2 - Allow user to select correct sheet if they have not, then click OK. If this cannot be done, then is it possible to place a cancel option in the box, so that they can select the correct sheet, then try again?
    2 - Import the data from the file selected into the active worksheet.
    3- Force a sort so that the format of the sheet matches the rest of the master workbook.


    Here is the code that I have so far.


    Private Sub BTNimportdata_Click()
    
    MsgBox "Be sure to select the correct destination worksheet before clicking OK."
    
    Dim customerBook As Workbook
    Dim filter As String
    Dim caption As String
    Dim customerFilename As String
    Dim customerWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Set targetWorkbook = Application.ActiveWorkbook
    
    filter = "Excel Files (*.xl*;*.xlsx;*.xlsm;*.xlsb;*.xlam;*.xltx;*.xltm;*.xlz;*.xla;*.xlt;*.xlm;*.xlw),*.xl*;*.xlsx;*.xlsm;*.xlsb;*.xlam;*.xltx;*.xltm;*.xlz;*.xla;*.xlt;*.xlm;*.xlw"
    caption = "Please Select an input file "
    customerFilename = Application.GetOpenFilename(filter, , caption)
    Application.ScreenUpdating = False
    
    Set customerWorkbook = Application.Workbooks.Open(customerFilename)
    Dim targetSheet As Worksheet
    Set targetSheet = targetWorkbook.Worksheets.ActiveSheet
    Dim sourceSheet As Worksheet
    Set sourceSheet = customerWorkbook.Worksheets(1)
    LRs = sourceSheet.Cells(Rows.Count, "A").End(xlUp).Row
    LRt = targetSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    If LRt = 2 Then LRt = 1
    sourceSheet.Range("A1:BZ" & LRs).Copy targetSheet.Range("A" & LRt)
    customerWorkbook.Close
    
    Dim objSort As Sort
        Dim Rng As Range
        
            With ActiveSheet
                Set Rng = .UsedRange
                Set objSort = .Sort
            End With
            
                With objSort
                    .SortFields.Clear
                    .SortFields.Add Key:=Columns("I"), SortOn:=xlSortOnValues, _
                                    Order:=xlAscending, DataOption:=xlSortTextAsNumbers
                    .SortFields.Add Key:=Columns("C"), SortOn:=xlSortOnValues, _
                                    Order:=xlAscending, DataOption:=xlSortTestAsNumbers
                    .SortFields.Add Key:=Columns("D"), SortOn:=xlSortOnValues, _
                                    Order:=xlAscending, DataOption:=xlSortTextAsNumbers
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SetRange Rng
                    .SortMethod = xlPinYin
                    .Apply
                End With
    Application.ScreenUpdating = True
    
    End Sub

    Any help with this would be greatly appreciated.

    Thanks,
    DaiyannaGrae

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Import data from workbook into Active Sheet, and Allow manipulation of WB around MsgBo

    Hello DaiyannaGrae,

    ihich box do you want to add Cancel to?
    How do you know if the user is on the correct worksheet?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    07-22-2013
    Location
    Norfolk, VA
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Import data from workbook into Active Sheet, and Allow manipulation of WB around MsgBo

    I want to add the option to the MsgBox. The intent for this file is that the user can update each week as the reports are availible, by selecting the appropriate week and importing the data that has been downloaded in an excel document. It will be up to the user to select the correct sheet, as this will be used several times each week for different groups. here is the document that I am working with, and an example of the data that will be imported.

    mock week 1.xlsx
    ZCN50 Weekly Tracker FY13.xlsm

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Import data from workbook into Active Sheet, and Allow manipulation of WB around MsgBo

    Hello DaiyannaGrae,
    ,

    Thanks for posting the workbooks. Having them reference is a big help.

    Here is the code to add a "Cancel" button to the message box...
        Dim Answer As Integer
    
            Answer = MsgBox("Be sure to select the correct destination worksheet before clicking OK.",vbOkCancel+vbExclamation)
            If Answer = vbCancel Then Exit Sub
    I am working on the other parts.

  5. #5
    Registered User
    Join Date
    07-22-2013
    Location
    Norfolk, VA
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Import data from workbook into Active Sheet, and Allow manipulation of WB around MsgBo

    Thank you for helping! That snippet works like a charm!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Data update in active sheet and also to master workbook through vba
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-22-2013, 08:42 AM
  2. Replies: 1
    Last Post: 04-08-2013, 02:37 AM
  3. Need a VBA Code to refresh data and save the active sheet as a new workbook.
    By Mysore in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-23-2012, 08:50 AM
  4. how can worksheet_activate event of active sheet work of active workbook
    By tahir mahmood in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2011, 08:19 AM
  5. Import workbook as worksheet in the active workbook
    By legendkiller420 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-21-2010, 06:07 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1