May 05 2019 03:56 AM
Hi All,
I am trying to achieve the following in VBA.
I have a master workbook called Master.xlsm.
I have a number of workbooks called Engineer1 to X
Engineer workbook only contains 1 worksheet (Sheet1)
I would like to be able to copy all the contents from Engineer1 workbook for all the Engineer workbooks to the Master Workbook.
I want to say copy rows 2 to 10 from Engineer 1 workbook and then copy rows 2 to 10 from Engineer2 workbook to the the cells below the data from engineer1.
I have tried a few examples from the internet but I cannot get the code to work.
Please help. I have attached my base files for your easy reference.
Vonryan
May 05 2019 05:57 AM
Your approach is possible but these days I would recommend the use of Power Query. Combining a set of files from a folder is core functionality of PQ and once further 'Engineer' files are added to the folder all that is needed is to click the refresh button. Ideally the data should all be contained in Tables
Data ribbon tab
Get Data
From File
From Folder
Choose 'combine'
Select the Table to import from each file
Edit as needed
Close and load to sheet within Master
May 05 2019 06:33 PM
Peter, thanks for your reply. I have tried your described method but I seem to be struggling.
I have one file Master.xlsm on D:\
I have four files Engineer1 thro Engineer4 on D:\Test\
I go to data ribbon and select New Query\From File\ From Folder and set the path to D:\Test
the query selects the 4 files and I press Combine and load.
THe combinne dialogue window appears and I select Example file as Engineer1. The prview pane show me the layout which is fine.
I press the OK button and the query starts running.
I then get a error message
[Expression.Error] The key didn't match any rows in the table..
Please see attached screenshot
What am I doing wrong please
Vonryan@Peter Bartholomew
May 06 2019 03:46 AM - edited May 06 2019 03:52 AM
SolutionThe screenshot is very close to that given by working versions.
One thought is that the unit of interaction between PowerQuery and Excel is the Table. I think it may be possible work with Named Ranges or the UsedRegion of a Sheet but they are not the preferred options.
Unless you have already done it, I would suggest going through each of your four files and converting the data to a Table [Ctrl/T]. This will give a default name of Table1 but it can be renamed to give some more meaningful reference such as 'EngineeringTask' (the same name in each file). Then, within the Combine & Edit dialogue, select the Table name to be the basis for extracting data from the sample file.
Fingers crossed this takes us a step forward!
May 06 2019 04:22 AM
@Peter Bartholomew , From Folder connector works fine with entire sheets. The only point data for all files in the folder have to have the same structure.
May 06 2019 07:10 AM
Thank you. From the selections available on the combine files menu, I suspected that was the case. I wouldn't have personal experience because I haven't used location (Sheet and cell notation) to reference data objects for a number of years now. Tables are simply a more natural starting point for me and, most recently, this is followed by spilt arrays in which only the anchor cell is named.
Oddly enough, it is sometimes difficult to establish a dialogue with Excel users; wonder why that is? ;)
May 06 2019 08:17 AM
That could be tables. With them even easier. But with that all your files are to be with exactly the same Table1 with exactly the same column names. Only values could be different. What is done, you take any file in the folder as a sample one, transform it (it could be Tables, Named ranges or entire sheet) and generated for such transformation function is applied to all files in the folder. If files have different structure you failed.
Oh, Excel users are quite different. Even if speak about the Excel, sometimes we speak on different languages. That's not good and not bad, that as it is.
May 10 2019 02:17 AM
Gentlemen,
You have been a great help. Thank you. I have achieved my goal thanks to your help.
I will investigate Tables when I get the chance.
Vonryan
May 10 2019 05:36 PM
Hi Peter,
I have a new challenge if you are interested.
I wish to hide culumns in a worksheet based on dates.
On worksheet 1 from B2:Q2 are cells with dates. Each cell is one week apart.
I have created a button on the page and it triggers a sub routine that is supposed to check the current date (Today()) function and evaluate this date into a week number using [Weeknum(Today()].
It then checks the cell Range (B2:Q2) date and evaluates these dates for [Weeknum(c.Value)]
If the week number of the date in the Range of cells B2:Q2 is <> to week number of today then hide the columns as I only want to see this weeks column.
I have written the following code to try and resolve my isssue by using a message box to return the values of the variables.
I cannot get the Week Number function to work.
Sub ShowCurrentWeekOnly()
Dim c As Range, msgValueC As Long, msgValueWeeknumC As Long, msgValueDate As Long, msgValueWeeknumDate As Long, WeekNum As Long
Application.EnableEvents = False
On Error Resume Next
For Each c In Range("B2:Q2")
msgValueC = c.Value
WeekNum = Evaluate("=weeknum(msgValueC)")
msgValueWeeknumC = WeekNum
msgValueDate = Date
msgValueWeeknumDate = [Weeknum(Date()]
MsgBox "Returned values are " & msgValueC & " " & msgValueWeeknumC & " " & msgValueDate & " " & msgValueWeeknumDate
If WeekNum <> [weeknum(Date())] Then
c.EntireColumn.Hidden = True
Else
c.EntireColumn.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub
The week number always returns zero.
I wonder if you can help.
May 11 2019 02:14 AM
The statement
WeekNum = [Weeknum(msgValueC)]
gave me an error 2029.
You could calculate the week numbers on the spreadsheet and then read them from the module using something of the form
WeekNumber = c.Offset(1).Value
Alternatively
WeekNum = Application.WorksheetFunction.WeekNum(msgValueC)
seems to work OK.
BTW, I hate seeing direct references to ranges in VBA. A user (could be you) inserts a row and the code crumbles. A named range is far safer.
May 11 2019 06:43 PM
Dear Peter,
Thank you for your help.
Final code looks like this and works fine.
Sub ShowCurrentWeekOnly()
Dim c As Range, WeekNumC As Long, WeekNumNow As Long
Application.EnableEvents = False
On Error Resume Next
For Each c In Range("B2:Q2")
WeekNumC = Application.WorksheetFunction.WeekNum(c.Value)
WeekNumNow = Application.WorksheetFunction.WeekNum(Date)
If (WeekNumC <> WeekNumNow) Then
c.EntireColumn.Hidden = True
Else
c.EntireColumn.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub
I will also do some research on Named Ranges as I do not know how to do this yet.
Vonryan
May 12 2019 07:52 PM
Dear Peter,
Just an update after my research on Named Ranges.
The code looks like this and everything is good. Thanks for the guidance.
Sub ShowCurrentWeekOnly()
Dim c As Range, WeekNumC As Long, WeekNumNow As Long
Application.EnableEvents = False
On Error Resume Next
For Each c In Range("LABOURRANGE")
WeekNumC = Application.WorksheetFunction.WeekNum(c.Value)
WeekNumNow = Application.WorksheetFunction.WeekNum(Date)
If (WeekNumC <> WeekNumNow) Then
c.EntireColumn.Hidden = True
Else
c.EntireColumn.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub
May 06 2019 03:46 AM - edited May 06 2019 03:52 AM
SolutionThe screenshot is very close to that given by working versions.
One thought is that the unit of interaction between PowerQuery and Excel is the Table. I think it may be possible work with Named Ranges or the UsedRegion of a Sheet but they are not the preferred options.
Unless you have already done it, I would suggest going through each of your four files and converting the data to a Table [Ctrl/T]. This will give a default name of Table1 but it can be renamed to give some more meaningful reference such as 'EngineeringTask' (the same name in each file). Then, within the Combine & Edit dialogue, select the Table name to be the basis for extracting data from the sample file.
Fingers crossed this takes us a step forward!