Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts

    Data from server application to master file

    Greetings,

    I have a link in an application that, when pressed, provides our team with data by opening a .csv file. Each day the file has different data but always in the same format. From there, we have to transfer it into a master file (which is always open on one workstation) so it becomes logged as a permanent record. To make the process easier, I have written a macro to do the transfer of data but the macro currently needs to be initiated manually. We are looking for is a way, if possible, to automatically transfer the data from the application directly into the master when the application link is selected. Any suggestions would be most grateful.

    Thank you in advance.
    Alexandra

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alexandra,

    What is the Application in question? If it as a MS Office application this can be easily done. If not it more difficult.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Alexandra,

    If your CSV file opens with the same name, then I would use WithEvents to create a WorkbookOpen event that monitors for the CSV file to open.

    Within your master file, create a new class module by opening the VB editor (F-11) > click insert at top > Class Module. Rename the class module to MonitorNewWB. Place the following code in the module.

    In the Class module:
    Code:
    Public WithEvents App As Application
    
     Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
        MergeWB Wb
     End Sub
    
    
     Private Sub Class_Terminate()
         Set App = Nothing
     End Sub

    In a standard module, place the following code:
    Code:
     Dim Watcher As MonitorNewWB
    
     Sub InitApp()
         'MUST BE INITIATED IN WORKBOOK_OPEN OR AFTER AN ERROR STATE
         Set Watcher = New MonitorNewWB
         Set Watcher.App = Application
     End Sub
    
    Sub MergeWB(wb2 As Workbook)
    
         'YOUR CODE GOES HERE
    
    End Sub

    In the ThisWorkbook module, place this code
    Code:
    Private Sub Workbook_Open()
        InitApp
    End Sub
    This is how it works:

    When you open your master workbook, the InitApp runs which initializes the Watcher variable as a new MonitorNewWB object which remains in scope as long as there is no error state. If your code anywhere in the workbook creates an error, just rerun the InitApp tied to a button with a caption "Start Watcher".

    When a new workbook is opened, the class procedure App_WorkbookOpen is run which starts the MergeWB macro and imports your open CSV into your master using the code you have already written. You can change the name of the macro to what you already have as long as it matched in the class procedure with the macro name in the standard procedure. Your macro should check the name of the newly opened workbook passed to it from the class procedure to determine if the correct file was opened and not some other workbook.

    Things to remember:
    1. The New instance of the class must be initiated (run InitApp macro) for the Watcher to start monitoring.
    2. The Watcher event will go out of scope if there is an error state so the InitApp must be rerun
    3. The App_WorkbookOpen class procedure passes the argument Wb to your procedure so make sure your procedure in the standard module is written to receive the parameter then use the name of the passed workbook to make sure it is the correct one.

    Let me know if you get stumped, however, this is easier than it appears.

    HTH,
    Maud

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    Alouso (2016-03-06)

  5. #4
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    Retired Geek,

    The report is generated from a Siemens server. We have the ability to input some parameters such as date range but no control over the format. Thank you for your interest.

    Maudibe,

    I followed your guidelines and made the addition/changes you specified. On my first click of the link to generate the CSV file called "CwReport.xls", the code picked up that a new file had opened but I received an error. I finally realized my mistake in which you specifically pointed out not to forget, was to use the Wb parameter to check the name of the file. Once I made the change, I was able to execute the creation of the CSV file which was immediately pulled into the master file instantaneously.

    Here is my adapted code.
    Code:
    Sub MergeWB(wb2 As Workbook)
    Application.ScreenUpdating = False
    Dim wb1 As Workbook
    Dim iCrpt As Integer, iMaster As Integer
    Dim lrow As Integer, erow As Integer
    If wb2.Name = "IRTv1.1.xlsm" Then Exit Sub
    If wb2.Name <> "CwReport.xls" Then GoTo errorhandler
    Set wb1 = ThisWorkbook
    If wb1.Worksheets(1).FilterMode = True Then
        wb1.Worksheets(1).Range("A2:F2").AutoFilter
    End If
    lrow = wb1.Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row + 1
    erow = wb2.Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row + 1
    For iMaster = 3 To erow
        For iCrpt = 2 To 7
            If iCrpt = 2 Or iCrpt = 7 Then
                wb1.Worksheets(1).Cells(lrow, iCrpt - 1) = CDate(wb2.Worksheets(1).Cells(iMaster, iCrpt))
                wb1.Worksheets(1).Cells(lrow, iCrpt - 1).HorizontalAlignment = xlLeft
            Else:
                wb1.Worksheets(1).Cells(lrow, iCrpt - 1) = wb2.Worksheets(1).Cells(iMaster, iCrpt)
                wb1.Worksheets(1).Cells(lrow, iCrpt - 1).HorizontalAlignment = xlLeft
            End If
        Next iCrpt
        lrow = lrow + 1
    Next iMaster
    Application.DisplayAlerts = False
    wb2.Close
    Application.DisplayAlerts = True
    FormatSheet
    Exit Sub
    errorhandler:
    MsgBox "The wrong workbook has opened"
    End Sub
    A little bit of fine tuning and now it works great. Brilliant!!! Thank you soooo...much

    Alexandra

Tags for this Thread

Posting Permissions

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