UPDATING QUESTION to include reference I found here***:
Macro button under customized ribbon tab tries to open old Excel file
I wrote a thing here last night that this is related to (Excel telling me it can't access a file I don't want it to access) I've got a Sub that turns a portion of a worksheet into a table and then sorts that table by a column, in preparation for a second procedure that will copy areas of this sheet onto another sheet.
I had been getting an error that said "we can't connect to 'https://...my.sharepoint../BETAV9_8_ItemAccountingTEMPLATEetcetc. Please make sure you're using correct web address."
Now the code is opening a completely different version of this file, that is, BETAV9_9, which I guess it found, unlike before, when it couldn't find BETAV9_8. The thing is, I have no idea what's making it open the other file. Here is what I'm doing:
Sub makeItemChecklistTable()
Dim selectRange As String
Dim lowerBound As Integer
Dim ws As Worksheet
Dim src As Range
Dim tbl As ListObject
Dim sortRange As Range
lowerBound = numberofCERTSRecords() + 1 'This function counts the number of records on another sheet, which is 147
selectRange = "B1:I" & CStr(lowerBound)
Set ws = ActiveWorkbook.Sheets("ItemChecklist")
Set src = ws.Range(selectRange)
ws.ListObjects.Add(SourceType:=xlSrcRange, Source:=src, _
xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleLight1").Name = "keywordChecklist"
'Item Checklist table is called keywordChecklist. Before exporting to PTF, sort keywordChecklist by Status in Ascending order
Set tbl = ws.ListObjects("keywordChecklist")
Set sortRange = ws.Range("keywordChecklist[ED_Question_ID]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortRange, Order:=xlAscending
.Apply
End With
End Sub
I mentioned in last night's post that I used MS' guide for finding references to an external Excel file (https://support.microsoft.com/en-us/office/find-links-external-references-in-a-workbook-fcbf4576-3aab-4029-ba25-54313a532ff1?ui=en-us&rs=en-us&ad=us) but at least in all the places I looked, I can't find anything. And if there were a reference to an old file, why would the version before this one try to open BETAv9_8 and now the current workbook is successfully opening BETAv9_9?
When I stepped through this procedure, it inexplicably jumped (2000 times) to another procedure used to modify 2000 strings on a separate worksheet (it takes a string and returns the same string minus a few characters). That worksheet is referred to by the current worksheet via a VLOOKUP function but by the time this procedure has run, all those VLOOKUPs have already been looked up on the current page, i.e. they are displayed already in column H. I'm sorting by column E (ED_Question_ID).
***UPDATE I do see that I have buttons assigned to macros on my custom toolbar that refer to older versions of this same file. I can fix this using the Export UI function described on this page (Macro button under customized ribbon tab tries to open old Excel file) but 1. I wasn't calling this macro from a button; I was running the procedure directly from the vba code window, and 2. Like I said I've been saving these files in sequence, raising the beta number every time I make a major change. Am I going to have to export and rename all the buttons/toolbar shortcuts every single time I save as a new file name? This seems like an absurd way to set things up by Microsoft.
