Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts

    Macro to open up file and update files

    Macro to open and update several workbooks



    I have the following code which is used to open up workbooks one by one and to update these . The source workbooks in CSV format are in the folder C:\downloads. However, I need to open each file individually and then select the file in C:\downloads in order for the destination file to be updated

    The downloaded files are in C:\downloads and the destination workbooks are in C:\Parts & SVC Sales

    The workbooks in C:\Parts & SVC Sales contains and name and division

    for eg Br1 parts sales , Br1 Service Sales

    When opening the files , the files for parts sales for EG Br1 Parts Sales must be updated to file containing a similar name (in C:\downloads) and must contain in the file name "salesperson" for eg Br1 Salesperson 01-07-2014


    When opening the files , the files for service sales for eg Br1 Service sales must be updated to file containing a similar name (in C:\downloads) + must contain in the file name "" for eg Br1 Service order repair register 01-07-2014

    When comparing the name in the destination file to the source file, the name can be anywhere in the source file

    Code:
     Sub Update_Workbooks()
       ChDir ("C:\Parts & SVC Sales")
        Application.DisplayAlerts = False
        fPath = Application.GetOpenFilename
        fName = Mid(fPath, Len("C:\Parts & SVC Sales") + 2)
        Workbooks.Open (fPath)
        Application.Run "'" & fName & "'!Auto_Update"
            ActiveWorkbook.Save
            ActiveWorkbook.Close
            CloseCSV
    End Sub
    See link to file examples


    https://www.dropbox.com/s/dwf8ytgnnf79uia/Dropbox.rar

    Your assistance in resolving this is most appreciated

    I have also posted on MrExcel.com


    http://www.mrexcel.com/forum/excel-q...workbooks.html

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    Howard

    Your request is not clear enough to get a great response. Can you have another go at explaining exactly what you want to do with the 4 files you loaded to dropbox?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Andrew

    Currently I use the macro Sub Update_workbooks in a separate workbook to allow the user to select one of the files in the directory C:/Parts & SVC Sales for eg Br1 Service Sales that needs to be updated with the latest information. The user will then be prompted to select the downloaded csv file in the directory C:/downloads that contains the same name and also has Service order repair register in the name in the case of Service sales

    When prompted to open up a file containing parts sales for eg Br1 Parts Sales, the user will then be prompted to select the downloaded csv file in the directory C:/downloads that contains the same name and also has "salesperson" in the name in the case of Parts sales


    Instead of opening up each file individually in C:/Parts & SVC Sales and selecting the appropriate csv file in C:/downloads using the Update_Macro, I would likea macro to open up all the files in the directory C:/Parts & SVC Sales and update these with the csv files where the name and type matches for eg Br1 Parts Sales to be updated with csv file Br1 Salesperson 01-07-2014 (the date in the file is not important for match the parts file -the name and salesperson is)


    Hope this is clearer
    Last edited by HowardC; 2014-08-07 at 21:52.

  4. #4
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Andrew

    Have you had a chance to have a look at my problem ?

  5. #5
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    Howard

    I have looked at the files and also looked at the two other forums where you have posted your request. I haven't yet been able to arouse the enthusiasm required to have a go at this problem. I note also some very knowledgeable and helpful forum users have also looked at this thread and decided the same. Since you haven't gotten useful responses on three different forums, lets look at why that might be.

    Generally, people post solutions when they understand the problem and can suggest a solution. People prefer to answer little questions with simple answers that help the OP get past a roadblock. Complicated problems can get awesome solutions but that does require the forum respondents to do considerable amounts of unpaid work to help out a stranger. This is more likely to happen if the problem appears interesting and might relate to some problem we might have/had ourselves.

    The major reasons I see with you not getting useful responses to your request is:
    1. This is clearly something you are getting paid for and we are not
    2. This problem is poorly described and not enough sample files are provided
    3. The scope of the problem appears quite large (possibly because of point 2)
    4. You have posted to multiple forums and so many people here think someone else will make the effort

    The information you have given us here is insufficient for us to actually understand the problem. The best description of what you require looks like it is this one http://www.ozgrid.com/forum/showthre...747#post722747. This description is pretty much the opposite of what I thought you were trying to do from the descriptions here.

    My suggestion is to either pay someone to listen, question, analyse and solve your issue OR break it down into small enough chunks that the scope becomes clearly describable. A simple way to do this would be to just take on Part Sales - this effectively halves the scope and most likely the code developed for this would be directly applicable to the Service Sales. Ideally, you would outline a strategy on the steps required to get to the target and what you have done so far to solve each step. By chunking this down into small enough pieces, you chance of getting to the solution increases massively.

    Secondly, make it clear whether you are trying to export OR import csv files.

    Thirdly make it clear exactly what the parameters are. eg. What does 'KIN BR1 KIN BR1' mean? Where are the files? How are they named? Does each workbook correspond with a single salesperson and region? Does each csv file correspond to a single salesperson and region?

    Fouthly, explain why you need separate files. Why is this all not in a single workbook or database?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #6
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    hi Andrew

    Thanks for the your input. I have noted your comments and will have one more attempt and clarifying what I am attempting to do

  7. #7
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Andrew

    I will try to make this a simple as possible

    Each month I export files from our mainframe to a directory C:\extract. These files are automatically set to save as CSV files when exporting

    I have xlsx files in a directory called C:\parts & SVC sales

    I currently have a macro which allows me to open up the file in C:\parts & Services sales and to select the appropriate csv file in C:\Extract which then updates the data in the xlsx file that was opened

    The macro is as follows:

    Code:
     Sub Update Workbooks()
       ChDir ("C:\Parts & SVC Sales")
        Application.DisplayAlerts = False
        fPath = Application.GetOpenFilename
        fName = Mid(fPath, Len("C:\Parts & SVC Sales") + 2)
        Workbooks.Open (fPath)
        Application.Run "'" & fName & "'!Auto_Update"
            ActiveWorkbook.Save
            ActiveWorkbook.Close
            CloseCSV
    End Sub

    The Auto_Update macro is as follows:

    Code:
     Sub Auto_Update()
    Clear_Sheets
    Open_Workbook
    Pivot_refresh
    End Sub
    
    Sub Open_Workbook()
    ChDir ("C:\extract")
    Sheets(2).Delete
    
    Dim nb As Workbook, tw As Workbook, ts As Worksheet
    a = Application.GetOpenFilename
    If a = False Or IsEmpty(a) Then Exit Sub
    With Application
        .ScreenUpdating = False
        End With
    Set tw = ThisWorkbook
    Set ts = tw.ActiveSheet
    Set nb = Workbooks.Open(a)
    
    
    nb.Sheets(1).Copy After:=Workbooks("Br1 Service Sales.xlsm").Sheets(1)
     Sheets(2).Name = "Imported Data"
     ChDir ("C:\my documents")
     
     End Sub
    
    Sub Clear_Sheets()
    Sheets(2).Select
    With Range("A:Z")
    .ClearContents
    End With
    End Sub
    
    
    Sub Pivot_refresh()
    Sheets("pivot table").Select
    
    ActiveSheet.PivotTables("PivotTable5").RefreshTable
    End Sub

    I would to automate the process further so as to open up all the files in C:\parts & SVC sales and update these with the appropriate CSV file


    1) I want to update all of the files containing “Parts Sales”.xlsx in C:\parts & SVC sales with the appropriate CSV file in C:\extract . The branch name in the CSV file must be the same and it must also contain “Salesperson” in the name. for eg Br1 Parts sales.xlsm to be updated with Br1 Salesperson ,,,,, .csv
    2) I want to update all of the files containing ““Service Sales”.xlsx in C:\parts & SVC sales with the appropriate CSV file in C:\extract . The branch name in the CSV file must be the same and it must also contain Service order repair register” in the name. for eg Br1 Service sales.xlsm to be updated with Br1 Service order repair register.csv


    I have attached sample file

    Your assistance in this regard is most appreciated
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Andrew

    Just to let you know that I have resolved the problem, buy approaching it is small steps

    Once again thanks for pointing me in the right direction. It took me a while, but I am almost where I want to be

  9. #9
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,514
    Thanks
    3
    Thanked 143 Times in 136 Posts
    Well done Howard.

    Your perseverance has paid off.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

Posting Permissions

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