I am trying to write a macro that allows a user to select documents from a list to open (and then eventually be edited automatically).
I have an Excel spreadsheet with the macro and a worksheet listing all of the files. The macro reads the list of files and an inputbox pops up, allowing the user to select which files they want. The macro then passes the filenames onto another sub that opens each file (in Word). It's having no problem finding the files and opening Word, but it keeps telling the user that "[Filename] is loced for editing by '[username]'. Do you want to: Open a Read Only copy / Create a local copy and merge your changes later / Receive notification when the original copy is available", but the file isn't locked for editing!!! I don't get it!
I've tried using SendKeys to hit ENTER to continue along, but the prompt isn't the active window, so that does nothing and I can't use ALT+TAB because the number of times that needs to be used to get to the prompt is dependent on the number of other windows open. I've also used "wordapp.ReadOnly = True", but that doesn't seem to change anything. I've checked that the file is definitely NOT read-only, even creating brand new test files with the correct filename (i.e., created a new Word file, named it one of the filenames from the list, and saved it to the folder to the macro looks in).
Any ideas? Thanks in advance.
Here is my code:
Sub Compile_Specs()
SpecPrompt.Show
End Sub
-------------------------------
Sub SpecSheetPopulate(Tname As String)
Set wordapp = CreateObject("word.Application")
wordapp.documents.Open "C:\Users\bnieto\Documents\" & Tname & ".docx"
End Sub
-----------------
Private Sub Cancelprompt_Click()
UFpromptcancel = True
Unload Me
End Sub
------------------------
Private Sub OKprompt_Click()
Dim k As Integer
Dim Tname As String
Dim varItm As Variant
Dim strBuild As String
'Initial value should be row of first Spec listed
SpecRow = 2
For k = 0 To PromptList.ListCount - 1
If PromptList.Selected(k) = True Then
Tname = Worksheets("SpecList").Cells(SpecRow + k, 2)
Tname = Tname & " - " & Worksheets("SpecList").Cells(SpecRow + k, 1)
Call SpecSheetPopulate(Tname)
Application.ScreenUpdating = False
End If
Next k
Unload Me
UFpromptcancel = False
End Sub
---------------------
Private Sub UserForm_Initialize()
PromptList.Clear
Dim SpecRow As Integer
Dim strSpec
Sheets("SpecList").Select
'Initial value should be row of first Spec listed
SpecRow = 2
While Not IsEmpty(Cells(SpecRow, 1))
strSpec = Worksheets("SpecList").Cells(SpecRow, 2)
strSpec = strSpec & " - " & Worksheets("SpecList").Cells(SpecRow, 1)
PromptList.AddItem strSpec
SpecRow = SpecRow + 1
Wend
End Sub