Macro that takes data from 3 files and create a final file with restrictions

[Cris_93]

Hello Excel masters

I need your help in order to develop a final file (CustomerForecast) from 3 different files with some restrictions.

The final file should have the below format. The columns "FactType" and "Unit" should have always the same values.

Depot/StoreFormat	Product	FactType	Unit	Date	Value
Dublin	RTP088	Customer Forecast	Cases	02/11/2019	23
Belfast	RTB222	Customer Forecast	Cases	12/11/2019	32
Hatfield	RTP088	Customer Forecast	Cases	07/11/2019	30
Dordon	RTL008	Customer Forecast	Cases	05/11/2019	30
…	…	…	…	…	…

<tbody>
</tbody>

The other data for the others 4 columns come from the below 3 different files. I will put in red the required columns from each file:

"ROI.csv"

Supplier number

Category area

Star line

Tpnb

Description

Tpnd

Case size

Occ

Depot number

Depot name

Forecast date

Order date

Delivery date

Forecast cases

5997500

MFP

N

52440995

PORK MINCE 500G

31411873

8

5.05E+12

735

BALLYMUN FRESH PBL

03/10/2019

04/10/2019

05/10/2019

40

5997500

MFP

N

52445008

IRISH LAMB MINCE 15% FAT 533G

37216980

4

5.06E+12

735

BALLYMUN FRESH PBL

03/10/2019

04/10/2019

07/10/2019

10

5997500

MFP

N

52483552

T. ROUND STEAK BEEF MINCE 10% FAT 554G

34945576

12

5.06E+12

735

BALLYMUN FRESH PBL

03/10/2019

16/10/2019

17/10/2019

102

5997500

MFP

N

63755738

T. FIN* IRISH CANADIAN MAPLE RASHERS 240G

32607750

8

5.06E+12

735

BALLYMUN FRESH PBL

03/10/2019

21/10/2019

22/10/2019

48

…

…

…

…

…

…

…

…

…

…

…

…

…

…

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col span="2" style="text-align: center;"><col style="text-align: center;"><col span="3" style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

The Depot name "BALLYMUN FRESH PBL" should be picked as "Dublin" to the final file;

"NI.xls"

Supplier number	Occ	Tpnd	Description	Depot number	Depot name	Case size	Forecast date	Order date	Delivery date	Forecast cases
6242200	2.0955E+11	28798712	T.FIN 1 BEEF RIBEYE STEAK	835	NI BELFAST FRESH PBL	12	03/10/2019	19/10/2019	21/10/2019	2
6242200	2.0955E+11	28798712	T.FIN 1 BEEF RIBEYE STEAK	835	NI BELFAST FRESH PBL	12	03/10/2019	10/10/2019	11/10/2019	8
6242200	2.0955E+11	28798712	T.FIN 1 BEEF RIBEYE STEAK	835	NI BELFAST FRESH PBL	12	03/10/2019	05/10/2019	07/10/2019	21
6242200	2.0955E+11	28798712	T.FIN 1 BEEF RIBEYE STEAK	835	NI BELFAST FRESH PBL	12	03/10/2019	09/10/2019	10/10/2019	9
…	…	…	…	…	…	…	…	…	…	...

<tbody>
</tbody>

The Depot name "NI BELFAST FRESH PBL" should be picked as "Belfast" to the final file;

"Ocado.xls"

Forecast Delivery Date	Delivery Place	Order Group	SKU	Supplier Line Number	Product Description	Case Barcode	Forecast Order Qty (Cases)
06 October 2019 05:00:00	Dordon	Do: HFS	77760011	77760011	Ocado Lean Beef Steak Mince 5% Fat (500 GR)	15055004195	60
06 October 2019 05:00:00	Dordon	Do: HFS	296274011	296274011	Eden Beef Steak Mince 15% Fat (500 GR)	05391810242	38
06 October 2019 08:30:00	Hatfield	Ha: HFS	435984011	435984011	Eden Lean Beef Steak Mince 5% Fat (400 GR)	05391811102	51
07 October 2019 08:00:00	Erith CFC	Er: HFS	72581011	72581011	Ocado 4 Quarter Pounder Beef Burgers (454 GR)	15055002382	2
07 October 2019 08:00:00	Erith CFC	Er: HFS	402166011	402166011	Eden Aberdeen Angus Beef Roasting Joint (1.5 KG)	95391811068	6
…	…	…	…	…	…	…	…

<tbody>
</tbody>

As you for sure noticed the "Product" on the final file has a different format/id than in the three files "Tpnd" and "SKU". That's because for each product from our costumers we use a specific internal code (Retail code). The match between our suppliers code and our RT codes is made in a different file called "Week Forecast" and has the bellow aspect.

TPND	Code	Description
22918725	RPP001	T GAMMON STKS WT CARMELISED ONION MPQAS
	RPP001CP	TESCO GAMMON STEAK WITH CARMELISED CP
	RPP001WP	WIP TESCO GAMMON STEAK WITH CARMELISED
22918656	RPP002	TESCO BACON CHOPS WT MSTRD BUTTER MPQAS
	RPP002CP	TESCO BACON CHOPS WITH MUSTARD BUTTR CP
	RPP002WP	WIP TESCO BACON CHOPS WITH MUSTARD BUTTR
	RPP003	MARKET VALUE PALE BACK BACON JOINT
	RPP003WP	WIP MARKET VALUE PALE BACK BACON JOINT
	RPP004	MARKET VALUE PALE HAM FILLET
	RPP004WP	WIP MARKET VALUE PALE HAM FILLET
23966061	RPP005	TESCO THICK CUT SMKD RASHERS 250G MPQAS
	RPP005WP	WIP SMOKED THICKCUT BACK BACON RASHERS
23966147	RPP006	TESCO THICKCUT MAPLE RASHERS 250G MPQAS
	RPP006WP	WIP THICKCUT MAPLE BACK BACON RASHERS

<tbody>
</tbody>

Some of the codes are inactive. The only active code is the one in front of the Tpnd code. The macro should match the code from the supplier and pick the correspondent Retail Code to the final file.

Thanks a lot guys!

Kind regards,
Cristian

 

[Cris_93]

Hi there
On Ocado file the column that has the date is formatted as "General". I change it to "date" and ran again the macro but the issue persist.

I did what you asked and saved the file in .csv and opened with notepad and the date is stored as below:

Should it be saved as a number like "43190" or it actually should be a date?

Thanks for all your patience seriously,
Cristian

 

[pbornemeier]

If you format a column in your worksheet as Short Date and put =NOW() in it, does the date appear in dd/mm/yyyy format?
If you format a column in the Ocado workbook as Short Date and put =NOW() in it, does the date appear in dd/mm/yyyy format?

So it looks as if the dates in the Ocado.xls file are stored as dd/mm/yyyy, but according to this:
From:

VBA Macros - Region and Language

VBA Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

Date Literals
Date literals ignore the WRS [Windows Regional Settings] and are always in US format (mm/dd/yyyy)
When any other format different to mm/dd/yyyy is entered Excel will automatically convert it to the #mm/dd/yyyy# format.
It is much safer to avoid using date literals altogether and use VBA functions instead (DateSerial, DateValue)

I was wrong about how Excel interprets dates:

So perhaps if you insert a row in the Ocado file with a date that has to be in dd/mm/yyyy format (such as 30/01/2020 -- which looks terribly odd to me),
copy the date block, including that row, paste the date block in your worksheet it will "convince" Excel to see the rest of the dates in that format.
Then delete the 30/01/2020 row and continue processing.

I am hoping that when Excel sees the first date as dd/mm/yyyy it interprets the remaining dates as having the same format. There are a few other options to try mentioned in the code.

See if this update to your import code corrects the problem:

    'Your Code - Opening the Ocado File
    Workbooks.Open Filename:= _
        "\\IEDRGSFS01\data\Planning\production plans\production plans\Forecast from Tesco Connect\Ocado.xls"
    'Insert a blank row above the part that you are going to copy
    Range("A6:H6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    'Fill the cells of the blank row
    Range("A6:H6").Resize(1, 8).Value = Array(#1/30/2020#, "X", "X", "X", "X", "X", "X", "X")
    'Continue with your code, but including copying the added row
    btRw = Range("A65536").End(xlUp).Offset(0, 0).Row
    Range("$A$5:$H$" & btRw).Copy
    Windows(repFl).Activate
    Sheets("Ocado").Select
    btRw = Range("A65536").End(xlUp).Offset(0, 0).Row
    Range("A" & btRw).Offset(1, 0).Select
    ActiveSheet.Paste
    
    'May want to try either of the following to see if it helps instead of ActiveSheet.Paste
    '  if just the extra row did not help
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
    'Delete the added row
     Range(Range("A" & btRw).Offset(2, 0), Range("H" & btRw).Offset(2, 0)).Delete Shift:=xlUp
    
    'Continue with your code
    Application.CutCopyMode = False
    Cells.Select
    Cells.EntireColumn.AutoFit
    
    'What is this chunk for?
    Windows("Ocado.xls").Activate
    Range("$F$6:$F$" & btRw).Copy
    
    'Closing the file
    Windows("Ocado.xls").Activate
    Application.DisplayAlerts = False
    ActiveWindow.Close
    Application.DisplayAlerts = False

 

[pbornemeier]

Even easier" Add local:=True to your Workbooks.Open statement

Workbooks.Open Filename:= _
        "\\IEDRGSFS01\data\Planning\production plans\production plans\Forecast from Tesco Connect\Ocado.xls", local:=True

 

[Cris_93]

It is working now!!!! :D

YESSS!! Thank you so much my friend!!

You helped me so much!

Best regards,
Cristian