Excel boffins
#11
Ok cheers

I’m assuming all the staff are on a network. Firstly ensure that the option in the excel files are set to allow multiple users else changes to the files can be lost if two people have the same file open at the same time and both are making changes.

I’m sure when you move files the linked files automatically get updated with the new location.

Therefore you could copy files and move them to next months folder and the copied files will have changed the links automatically.  I’d need to test this but I’m sure that would work.

Are the data files (user input) to change monthly / quarterly or are they to use the same files each month?
Reply
#12
(11-23-2020, 06:46 AM)HawkingsHalfpint Wrote: Multi user access basically FF, plus these files get created, modified and utilised every day.

The files are item masters. Their data is used in other sheets also. But I need a ‘master’ master file in order to summarise, count and verify them easily.

I know already that the correct answer is a database, and that excel isn’t a database. MS Access would piss this, but I know fuck all about how to build databases (and is this is only a temporary thing until I invest in an ERP system). Excel is cheap and easy to use in the interim. Well, mostly!

If your files are in a consistent structure (Column headers) you could use Power Query or BI to pull them together. 

If they are all in the same drive folder or sharepoint location you can combine them in Power Query, this can also leave the file path/name as a separate column for reference. 

If its a summary, then load it into Power BI and refresh it, it will pull all the data from source and take very little time to refresh.

https://docs.microsoft.com/en-us/power-b...e-binaries
Reply
#13
Thanks both. Files are all in same directory. Not yet networked, but soon to be!

Will check out PowerBi (even though for some reason it has always reminded me of a big defensive female hockey player).
Reply
#14
Are the data input files required to start afresh each month / quarter or are they to be the same files each month / quarter?
Reply
#15
Yes and no FF! Every time we create, factor or make a new part we use a template file to record basically what it is, where from, how much, brief description etc. Also how many we have at one given time. That data is all in the same cells for consistency, so easy to source, it’s just the summary file I’m struggling with. As said above, Access would be ideal for this.

Can’t network just yet but server going in next week I hope, then we will be able to network. That’s when I’ll go full ERP (probably same system as sister company across North Sea), so no point throwing silly money at it now. However creating these files has had value, since the new ERP will be able to read this data in very easily. I just need better inventory visibility in the meantime.
Reply
#16
I’d say the sooner you create a database the better, even in Excel rather than a separate file for each record.

Have you considered creating a single table (as all records are the same) and then use pivot tables to produce your high level summary?
Reply
#17
This looks like it would probably do the job.  Alt+F11 to get into VBA.  Insert > Module.  Paste this code in.  Then....

=VLOOKUP(B2,pull("'C:\Users\Hawks\Docs\[" & A2 & ".xlsx]Sheet1'!A1:B200),2,FALSE)

Or just the pull bit if you need to grab a particular cell.

Code:
Function pull(xref As String) As Variant
Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, C As Range, n As Long
n = InStrRev(xref, "\")
If n > 0 Then
If Mid(xref, n, 2) = "\[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n > 0 Then b = b & Mid(xref, Len(b) + 2, n)
Else
n = InStrRev(Len(xref), xref, "!")
If n > 0 Then b = Left(xref, n - 1)
End If

If Left(b, 1) = "'" Then b = Mid(b, 2)
On Error Resume Next
If n > 0 Then If Dir(b) = "" Then n = 0
Err.Clear
On Error GoTo 0
End If

If n <= 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If

pull = Evaluate(xref)


If IsArray(pull) Then Exit Function

If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error GoTo CleanUp
Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add
On Error Resume Next
n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)

Else
For Each C In r
C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1, xlR1C1))
Next C

pull = r.Value

End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End If

End Function
Reply
#18
I appear to have strayed into a different universe/dimension. Serves me right for breaking the beads on my abacus.  Or the stale stilton after midnight.

I will say, modern mousetraps are much better than the block of wood and a springy thing.  Cleared half a dozen of the little fuggers the past two days.  Kestrels not doing their job.
Reply
#19
(11-23-2020, 10:29 AM)hudds Wrote: I appear to have strayed into a different universe/dimension. Serves me right for breaking the beads on my abacus.  Or the stale stilton after midnight.

I will say, modern mousetraps are much better than the block of wood and a springy thing.  Cleared half a dozen of the little fuggers the past two days.  Kestrels not doing their job.

Kip yower fuggin windas open!
Reply
#20
(11-23-2020, 10:18 AM)waffle Wrote: This looks like it would probably do the job.  Alt+F11 to get into VBA.  Insert > Module.  Paste this code in.  Then....

=VLOOKUP(B2,pull("'C:\Users\Hawks\Docs\[" & A2 & ".xlsx]Sheet1'!A1:B200),2,FALSE)

Or just the pull bit if you need to grab a particular cell.

Code:
Function pull(xref As String) As Variant
Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, C As Range, n As Long
n = InStrRev(xref, "\")
If n > 0 Then
If Mid(xref, n, 2) = "\[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n > 0 Then b = b & Mid(xref, Len(b) + 2, n)
Else
n = InStrRev(Len(xref), xref, "!")
If n > 0 Then b = Left(xref, n - 1)
End If

If Left(b, 1) = "'" Then b = Mid(b, 2)
On Error Resume Next
If n > 0 Then If Dir(b) = "" Then n = 0
Err.Clear
On Error GoTo 0
End If

If n <= 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If

pull = Evaluate(xref)


If IsArray(pull) Then Exit Function

If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error GoTo CleanUp
Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add
On Error Resume Next
n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)

Else
For Each C In r
C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1, xlR1C1))
Next C

pull = r.Value

End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End If

End Function

Cheers waffle. Appreciate the help (same to you FF and Mike). I got as far as the ("'C:\Users\Hawks\Docs\[" & A2 & ".xlsx]Sheet1'!A1:B200),2,FALSE) but was using INDIRECT rather than VLOOKUP (which I use for something totally separate).

Loads to try now! To programme that will keep me quiet while the Mrs watches programs about dwarves in LA. Seriously. 

Huggs - look away now.
Reply


Forum Jump:


Users browsing this thread: