VBA Code Help

[markster]

I've got a massive scheduling job to do from a spreadsheet with about 1300 lines and could really do with some VBA code to help me.

There are three sheets:

SHEET 1 - Details of working patterns - data is contained in columns A to W but I've hidden some columns so the screen shot isn't too big

SHEET 2 - Individual Weeks, Dates and Days of the week

SHEET 3 - The summary I need to be created by the Macro from Sheets 1 & 2

In order to create the summary I need in SHEET 3, it needs to look at 2 key columns in SHEET 1 (Column G - Week Range & Column Q Day Range) and Columns - A-F in SHEET 2. In the summary, it splits out out the week numbers into separate lines with actual dates and includes all data from SHEET 1 (I mean entire row of data A to W). I could try to explain it more in words but I think looking at the SHEET 3 screenshot illustrates what I need and trying to explain further might cause confusion. Obviously, I'm happy to answer any questions you may have.

SHEET 1 - Details of working patterns - data is contained in columns A to W but I've hidden some columns so the screen shot isn't too big

Sheet 2 - Individual Weeks, Dates and Days of the week

Sheet 3 - The summary I need to be created by the Macro from Sheets 1 & 2 (Desired Output)

Thanks
Mark

 

[bobsan42]

I'm certainly not an expert in the M-language. There a probably more elegant solutions. But I'm still learning. Just there are too many things to do and learn.

 

[markster]

hi @bobsan42 - hope that you are well. In post number 5 above you say

Then you need to create 3 Data sets and load them as Connections only using PowerQuery:

I've been trying to figure out how to do this but can't - can you talk me through the steps? Much appreciated Mark

 

[markster]

Hi @bobsan42 or anyone else who can help . This urgent scheduling job got put on one side as I had to rescue a different project but now it's become urgent again. I want to use your solution that's described in post #5. I created my 3 tables exactly as you specified. Then you say "create 3 Data sets and load them as Connections only using PowerQuery". I've been trying to figure out how to do this bit, but there's no youtube videos that I can find that might help and couldn't find any help on the web so I was wondering if you could describe the steps I need to do for this bit. Thanks in advance - Mark

 

[bobsan42]

Sorry, i cannot make a guide for you since I am not with a computer these days.
I am surprised you cannot find it online.
I will improvise on my mobile.
Select a table. Go to data tab / get and transform from table.
This shall take you to the power query editor.
Under Close and load select Load To ...
In the next window select Connection Only.

However, in post #9 i suggested a possible solution with all-in-one query. It transforms the data without the need for others. Plus it takes into account the new caveats from post #8. Can't you implement it?

 

[markster]

Sorry, i cannot make a guide for you since I am not with a computer these days.
I am surprised you cannot find it online.
I will improvise on my mobile.
Select a table. Go to data tab / get and transform from table.
This shall take you to the power query editor.
Under Close and load select Load To ...
In the next window select Connection Only.

However, in post #9 i suggested a possible solution with all-in-one query. It transforms the data without the need for others. Plus it takes into account the new caveats from post #8. Can't you implement it?

ok thanks @bobsan42 in the post 9 do i put the individual workbooks that link in a single folder? Thanks again. Mark

 

[markster]

Also I was a bit confused about this instruction - Pick a cell in your workbook, name it nmStart (Scope: Workbook) and fill in the starting date of of the first week maybe 27-09-2021 . I put workbooks, 1, 2, 3 4 in the same folder?

 

[bobsan42]

Also I was a bit confused about this instruction - Pick a cell in your workbook, name it nmStart (Scope: Workbook) and fill in the starting date of of the first week maybe 27-09-2021 .

I don't understand what is confusing.
Instead of listing all dates and days in sheet2 you only need the first day date . The rest will be calculated.

 

[bobsan42]

I put workbooks, 1, 2, 3 4 in the same folder?

It doesn't matter. Wherever you place them you have to fill the correct paths in the code below on the lines for src1 to src4:

let
    wdays = Table.TransformColumnTypes(Table.FromRows({{"Monday",0},{"Tuesday",1},{"Wednesday",2},{"Thursday",3},{"Friday",4},{"Saturday",5},{"Sunday",6}},{"Day","Num"}),{{"Day", type text}, {"Num", Int64.Type}}),
    sn = Number.From(Excel.CurrentWorkbook(){[Name="nmStart"]}[Content]{0}[Column1]),

    src1 = Table.PromoteHeaders(Excel.Workbook(File.Contents("C:\Users\...\Desktop\Workbook1.xlsx"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]),
    src2 = Table.PromoteHeaders(Excel.Workbook(File.Contents("C:\Users\...\Desktop\Workbook2.xlsx"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]),
    src3 = Table.PromoteHeaders(Excel.Workbook(File.Contents("C:\Users\...\Desktop\Workbook3.xlsx"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]),
    src4 = Table.PromoteHeaders(Excel.Workbook(File.Contents("C:\Users\...\Desktop\Workbook4.xlsx"), null, true){[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]),
    AllSource = Table.Combine({src1,src2,src3,src4}),

    Source = Table.NestedJoin(AllSource, {"Day"}, wdays, {"Day"}, "tbWeekdays", JoinKind.LeftOuter),
    #"Expanded tbWeekdays" = Table.ExpandTableColumn(Source, "tbWeekdays", {"Num"}, {"Num"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Expanded tbWeekdays", {{"WeekRange(s)", type text}}, "bg-BG"), {{"WeekRange(s)", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "WeekRange(s)"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"WeekRange(s)", type text}, {"Duration", type time}, {"Finish time", type time}, {"Start time", type time}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "WeekRange(s)", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"WR1", "WR2"}),
    typechange1= Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"WR1", Int64.Type}, {"WR2", Int64.Type}}),
    replace1 = Table.ReplaceValue(typechange1,null, each _[WR1],Replacer.ReplaceValue,{"WR2"}),
    listnums = Table.AddColumn(replace1,"CustomList",each List.Numbers([WR1]-1,[WR2]-[WR1]+1)),
    expandList = Table.ExpandListColumn(listnums, "CustomList"),
    #"Added Custom" = Table.AddColumn(expandList, "Custom", each [CustomList]*7+[Num]+sn),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Date"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Col1", "Activity Code", "Column1", "Activity", "Column2", "Column3", "Date", "WR1", "WR2", "Column4", "Shift Worker", "Zone", "Column5", "Column6", "Column7", "Column8", "No in Room", "Room", "Day", "Start time", "Finish time", "Duration", "Column9", "Column10", "Reference", "Num", "CustomList"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"WR1", "WR2", "Num", "CustomList"})
in
    #"Removed Columns"

Discard the code from post#9 there is a mistake in it .

 

[markster]

Got you - thank you @bobsan42