Hello everyone, I am new to this forum. I have searched the internet everywhere looking for a solution to link to a closed workbook with no success. The only solutions i found would require installing 3rd party Add-ins which wont work, since this is a work computer, and i want to pass this file off to others to run daily off their computers.
Problem: = i have a link to a closed workbook on sharepoint, and it the link changes daily based upon the date:
- 'http://......07-07-2018 this link works, but i would always have to change the date manually
- if i use use LINK & Celldate to create a text string then do indirect: indirect('http://......07-07-2018) does not work because workbook closed
Solution with SIMPLE VBA ... have a cell that puts together the string: =" 'http://...."& Celldate
Then copy and paste that cell values into the cell you want it in, then press F2 then press Enter, and it will calculate.
Range("AL3").Copy
Range("K3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
SendKeys "{F2}", True
SendKeys "{Tab}", True
This should solve a lot of peoples problems out there who wanted this issue solved for a single cell.
Now for the hard part ... i need to do this for several cells everyday on the same sheet.
It seems as thought the VBA runs first but the send keys doesnt send until after the whole vba is complete.
So in other words it will paste all my values, then after the VBA is complete then i see F2 Enter F2 Enter F2 Enter ...
Reading online, it seems as though this is a common problem and there is no solution.
The application wait doesnt work, the TRUE at the end of the sendkeys doesnt work etc.
VBA takes up all resources and the sendkeys doesnt happen till after.
Any Thoughts?
Problem: = i have a link to a closed workbook on sharepoint, and it the link changes daily based upon the date:
- 'http://......07-07-2018 this link works, but i would always have to change the date manually
- if i use use LINK & Celldate to create a text string then do indirect: indirect('http://......07-07-2018) does not work because workbook closed
Solution with SIMPLE VBA ... have a cell that puts together the string: =" 'http://...."& Celldate
Then copy and paste that cell values into the cell you want it in, then press F2 then press Enter, and it will calculate.
Range("AL3").Copy
Range("K3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
SendKeys "{F2}", True
SendKeys "{Tab}", True
This should solve a lot of peoples problems out there who wanted this issue solved for a single cell.
Now for the hard part ... i need to do this for several cells everyday on the same sheet.
It seems as thought the VBA runs first but the send keys doesnt send until after the whole vba is complete.
So in other words it will paste all my values, then after the VBA is complete then i see F2 Enter F2 Enter F2 Enter ...
Reading online, it seems as though this is a common problem and there is no solution.
The application wait doesnt work, the TRUE at the end of the sendkeys doesnt work etc.
VBA takes up all resources and the sendkeys doesnt happen till after.
Any Thoughts?
