VERY Difficult VBA question

[tljenkin]

Hi Guys,

I dont have the HTML maker so I am going to try to best explain this with words and I will make up an example for ease. Thanks!

Ok I have a master file that is linked to 2 feeder files.
Its January and a January folder has been created and the master file copied over.
However the 2 feeder files come from another source and the current month convention is reflected in them "MMYY". These feeder files have their own folder, within which is a date hierarchy of holders.

Without having to either manually update links, how can I use a macro that will do the following:

1) Look at a drop down in the master file and pick up the current month "MMYY"
2) Use the month above to detect which folder to look in
3) Be smart enough to substitute last month's name in the file "1214" with "0115" and link the master file to the correct feeder file

PLEASE NOTE: The reality is there is a 100 feeder files and 1 master file, so imagine how painful this is every month especially as there is very complicated sub folder structure where the feeder files can be found!!

 

[shg]

You can do the same thing via code:

ActiveWorkbook.ChangeLink oldLink, newLink, xlLinkTypeExcelLinks

It's just a question of composing the link strings. It's not clear how month and year (0115) gets you a date (01. Jan 2015), though. Or is the "01" redundant to the month?

 

[tljenkin]

in 0115, 01 is January, in 01. Jan 2015, 01 is indeed redundant but for the purposes of a folder structure, the folders are labelled 01-12 so that the month ordering visually is correct. I am afraid that cant change in the string. Thanks

 

[shg]

Maybe (untested)

Sub tlj()
  Dim t             As Date
  Dim avsLink       As Variant
  Dim asLink()      As String
  Dim iLink         As Long

  Dim sOld          As String
  Dim sNew          As String

  Dim iDir          As Long
  Dim nChg          As Long

  t = CDate(20 & Format(Range("A1").Value, "00-00"))

  With ActiveWorkbook
    avsLink = .LinkSources
    For iLink = 1 To UBound(avsLink)
      sOld = avsLink(iLink)
      asLink = Split(sOld, "\")
      For iDir = 1 To UBound(asLink) - 1
        If asLink(iDir) Like "####" Then
          asLink(iDir) = Format(t, "yyyy")
          asLink(iDir + 1) = Format(t, "mm. mmm yyyy")
          sNew = Join(asLink, "\")
          .ChangeLink sOld, sNew
          nChg = nChg + 1
          Exit For
        End If
      Next iDir
    Next iLink
  End With

  MsgBox "Changed " & nChg & " links"
End Sub

 

[Arithos]

I would use a macro to store information/get information. And skip external references entirely. (I did something similar for my work)

 

[tljenkin]

Hi Shg

Is the code above based on this path structure below? I am just about to test it. Thanks

X:\01 - Finance\Consolidated View\Trackers\2014\12. Dec 14\T1 Partners\JCI\UKMEA

 

[tljenkin]

I have tried it just now... the error I get is thus:

runtime error 13
type mismatch

In the VBA editor, this line is highlighted yellow: t = CDate(20 & Format(Range("A1").Value, "00-00"))

Please help debug it, thanks

Maybe (untested)

Sub tlj()
  Dim t             As Date
  Dim avsLink       As Variant
  Dim asLink()      As String
  Dim iLink         As Long

  Dim sOld          As String
  Dim sNew          As String

  Dim iDir          As Long
  Dim nChg          As Long

  t = CDate(20 & Format(Range("A1").Value, "00-00"))

  With ActiveWorkbook
    avsLink = .LinkSources
    For iLink = 1 To UBound(avsLink)
      sOld = avsLink(iLink)
      asLink = Split(sOld, "\")
      For iDir = 1 To UBound(asLink) - 1
        If asLink(iDir) Like "####" Then
          asLink(iDir) = Format(t, "yyyy")
          asLink(iDir + 1) = Format(t, "mm. mmm yyyy")
          sNew = Join(asLink, "\")
          .ChangeLink sOld, sNew
          nChg = nChg + 1
          Exit For
        End If
      Next iDir
    Next iLink
  End With

  MsgBox "Changed " & nChg & " links"
End Sub

 

[tljenkin]

just one other thing to point out, the drop down with the dates MMYY are entered as text as 0115 in excel will appear 115 unless it is written as '0115. Could this be why there is a bug in the macro?

 

[shg]

What's in A1 when the error occurs?

 

[tljenkin]

just the date 1214 but this is in text format so has a ' in front of the numbers. I have also tried running the macro with 1214 entered as number. Same error, thanks

 

[maropoly]

Did you ever get the solution to this issue tljenkin. I am actually in the same situation with a file wherei have to update 40 linked feeder files to a master financial file (Income Statement). And the monthly process of moving it from warehouse1015.xls to warehouse1115.xls is tedious and cumbersome. would like to know if there is a way to just change one value into a form ( i.e. enter December to produce 1215) in order to change all the link values. any suggestions?