Updating Workbooks

[howard]

I have the following code which is used to open up workbooks and to update these pertaining to files in C:\downloads. However, I need to open open neach file and then select the file in C:/extract so that it is ipdated

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

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 + 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 + must contain in the file name "" for eg Br1 Service ooder repair register 01-07-2014

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

 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

 

[CharlesChuckieCharles]

Sort of missing the code from Auto_Update

also show the actual file names for the diffrent types ( including extensions) might help with the understanding

 

[howard]

Hi Charles

Thanks for the reply

The Auto_Update cose is as follows:

 Sub Auto_update()
Clear_Sheets
Open_Workbook
Refresh_Pivot
Close_CSV
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("WM 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 Refresh_Pivot()

Sheets("Pivot table").Select

ActiveSheet.PivotTables("PivotTable5").RefreshTable
End Sub


Sub Close_CSV()
Dim WBK As Workbook
For Each WBK In Workbooks
If WBK.FileFormat = xlCSV Then
WBK.Close
End If
Next

End Sub

See below examples of destination files

BR1 Br1 Service RepairOrderRegister.csv (when exporting the file from main system it is exported like this) (The source file will be be for eg BR1 Services sales.xlsm
BR1 SalesPerson - 01-07-14 - 31-07-14.csv (the source file will be BR1 Salesperson.xlsm)

 

[howard]

Hi Charles

Have you managed to have some time to have a look at the additional information which I posted?. If you need further info, I could post a link to the files in drop box

Howard

 

[howard]

See link to sample files

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

 

[howard]

I have also posted on Windowssecrets

Macro to open up file and update files | Windows Secrets Lounge

 

[CharlesChuckieCharles]

Sorry I don't click links too dangerous

Maybe you should give another go at describing what it is you want ( for instance C:\extract whats that used for ) maybe describe the process rather than get bogged down in the naming

IE I have daily files I down load and I want each file stored a worksheet in a master workbook

 

[howard]

Hi Charles

Thanks for your patience

I will attempt to give it another go. If still not clear, send me a PM and I will email you the sample files

The files in C:\extract are csv files which have been exported. The files in C:\Parts & Service sales need to be updated with the CSV files by the macro Auto_Update

There are two types of files in C:\parts & Service sales folder , One containing Parts sales and the other containing Service Sales for eg BR1 Parts Sales , Br1 Service Sales

The workbook Br1 Parts Sales must update for eg Br1 Salesmen (name & Salesman is important) and BR1 Service Sales Must update Br1 Service order repair register (name and Service order repair register is important)

 

[howard]

Have also posted at link below


Macro to open up several files and update data