Reputation: 4 Apprentice
- Active Members
- Active Posts:
- 94 (0.05 per day)
- 16-October 09
- Profile Views:
- Last Active:
- May 19 2015 12:59 AM
- OS Preference:
- Favorite Browser:
- Favorite Processor:
- Favorite Gaming Platform:
- Your Car:
- Dream Kudos:
26 Aug 2011 - 06:51
Posts I've Made
Posted 15 May 2015Thanks for the link, I've got it to stop producing the error now, but for reasons I can't see, it mangles the link, causing Excel to prompt the user for the correct path & sheet for "update values". The code is now as follows:
Dim strFileToOpen as String strFileToOpen = "False" Do strFileToOpen = Application.GetOpenFilename(Title:="Please choose a file to open", FileFilter:="Excel Files *.xls* (*.xls*),") If strFileToOpen = "False" Then Response = MsgBox("No file selected", vbRetryCancel + vbExclamation) If Response = vbCancel Then Exit Sub End If End If Loop While strFileToOpen = "False" Workbooks.Open Filename:=strFileToOpen ... Range("AP2").Select ' MsgBox strFileToOpen ActiveCell.Formula = "=VLOOKUP(S2,'" & strFileToOpen & "extract'!$AP:$AP,1,FALSE)"
Not sure what's gone on with the formatting there...
Anyway, I was expecting the output in that cells to be:
Quote=VLOOKUP(S2,'[5th May 2015 WiP extract.xlsx]extract'!$AP:$AP,1,FALSE)
What I get is:
Quote=VLOOKUP(S2,'S:\XXXXXX\[5th May 2015 WiP extract.xlsxextract]extract'!$AP:$AP,1,FALSE)
Ignoring the network path (can't see how that would be the issue necessarily, although it is unnecessary as the extract is an open workbook). It's a bit confusing with all the "extract" in there - both the last word in the path, and the sheet are called extract. I tried referring to a named range on the "5th May.." workbook, but that failed too.
Might have discovered a clue - before going through the "update values" prompts that Excel throws, I can see the formula in the formula bar, and it's got the path to the workbook correct, but the name of the workbook is wrong (it has "extract" concatenated onto the end of the filename. Also, it has the sheet down as "5th May..." filename, without the xlsx extension). I've no idea where it's got all that, as the Msgbox display of the variable is correct.
Posted 12 May 2015It does, it is a Windows network path. The lookup works if manually created in the cell, it doesn't work (or even attempt to get created) from the VBA macro
Posted 11 May 2015I've set the ActiveCell using the Range.Select command immediately, not sure where the problem is? I do use Windows(2).Activate to switch between the workbooks (as the name can vary, I can't hardcode it), but the Range.Select still works, so I guess there's no problem?
If by "step through", you mean F8, then yep, I use it all the time
Posted 8 May 2015Fast reply not working for some reason...
The error is the same as the OP - "Application-defined or object-defined error", there's no output to the cell as the macro breaks at this point. It even breaks using activecell.value rather than formula! I wonder if it's something to do with the expansion of the variable it doesn't like?
Posted 8 May 2015Thanks for the reply! Unfortunately, it's still not playing. I've tweaked the code to read:
strVLFilename = "[" & strFileToOpen & "]" MsgBox strVLFilename Range("AP2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(S2,'" & strVLFilename & "extract'!$AP:$AP,1,FALSE)"
strVLFilename is defined as a string, and the msgbox confirms the path is correct. The other workbook is also open (fails whether closed or open), and the network path is accessible.
Another other ideas?
- Member Title:
- D.I.C Head
- 31 years old
- August 31, 1983
- Portsmouth, UK
- Programming Languages:
- Website URL:
chemicalfan hasn't added any friends yet.