Need to open an excel file manually in the middle of a macro

Question

First time posting so please be kind.

From a template file, I am running a macro to create a new folder with a copy of the template file in it. I then rename it and update it. At one point, I need to manually download a file from a website and open it and then start another macro to finish the update.

I initially tried to do that from one unique macro but I got issues as the macro would keep running before the excel file had time to open.

I have now split my macro in 2. At the end of the 1st macro, I call a userform with instructions and a continue button. The idea is that I would download the file while the userform is opened and click on "continue" when the file is opened.

For some reason, the file does not open at all. It seems like either the userform or the macro stops the file from opening. However, If I run it using the debug function, It works fine...

Public strSN As String, strPart As String, strPath As String


Sub create_new()


' Create Folder if it doesn't exist
'Dim strSN As String, strPart As String, strPath As String

'strSN = SerialNumber.Value
'strPart = PartNumber.Value
'strPath = "M:\Quality\QUALITY ASSURANCE\DOC\Rental Folder\Scanned MRB's\"

' close userform
welcomeform.Hide

'set Microsoft scription runtime reference to allow creation of folder macro

On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGUID "{420B2830-E718-11CF-893D-    00A0C9054228}", 1, 0
On Error GoTo 0

If Not FolderExists(strSN) Then
'Serial Number folder doesn't exist, so create full path
FolderCreate strPath & strSN

End If

' Create new file in new folder

On Error Resume Next

ActiveWorkbook.SaveCopyAs Filename:=strPath & strSN & "\" & strPart & " " & strSN & " " & "SNR.xlsm"

If Err.Number <> 0 Then
    MsgBox "Copy error: " & strPath & "TEMPLATE SNR.xlsm"
End If
On Error GoTo 0

' open new file without showing it or opening macros

Application.EnableEvents = False   'disable Events
Workbooks.Open Filename:=strPath & strSN & "\" & strPart & " " & strSN & " " & "SNR.xlsm"
Application.EnableEvents = True    'enable Events

' Modify serial number and part number in traceability summary form

Sheets("Traceability Summary Form").Activate
Sheets("Traceability Summary Form").Unprotect
Range("A7").Value = strSN
Range("C7").Value = strPart

' update file with ITP

Call Download_itp

End Sub

Sub Download_itp()

downloaditp.Show

End Sub

In the download_itp userform:

Sub continue_Click()

Call update_traceable_items

End Sub

Then the 2nd macro starts with code:

Sub update_traceable_items()
'
' Macro to update the SNR tab with the traceable items from the ITP
'

downloaditp.Hide


' copy ITP in file

Application.ActiveProtectedViewWindow.Edit
ActiveSheet.Name = "ITP"
ActiveSheet.Copy after:=Workbooks(strPart & " " & strSN & " " & "SNR.xlsm").Sheets("SNR template")

Any help would be appreciated! Thanks

Do you have to manually download the file? It's possible (for most files I believe) to have VBA download file X to folder Y. — BruceWayne
– BruceWayne, Commented Dec 1, 2015 at 15:40
@findwindow, I tried 'application.wait' before and it did not work. — charlotteF
– charlotteF, Commented Dec 1, 2015 at 15:41

David Zemens · Accepted Answer · 2015-12-01 16:09:47Z

1

The UserForm is being displayed modally, which probably prevents you from "opening" the recently downloaded file. When UserForm is displayed modally, the user is prevented from "interacting" with any part of Excel Application that is not the UserForm itself -- so you can't select cells or worksheets, you can't open files or close files, etc.

This is the default behavior for UserForms, but fortunately there is an optional parameter for the .Show method which allows you to display the form "modelessly":

downloaditp.Show vbModeless

This allows you to interact with the Excel Application while the form is open.

Note: If the file is on a shared network location, you can probably handle this better by using a FileDialog object to allow you to "browse" to the location of the file, and open it, all within the scope of your main procedure, like:

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Show

    If .SelectedItems.Count <> 1 Then
        MsgBox "No file selected!", vbCritical
        Exit Sub
    Else
        Dim NewWorkbook as Workbook
        Set NewWorkbook = Workbooks.Open(.SelectedItems(0))
    End If
End With

edited Dec 1, 2015 at 16:09

answered Dec 1, 2015 at 15:50

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BruceWayne Over a year ago

What is downloaditp? The userform name? Where does downloaditp get declared? (I'm not that familiar with userforms, so it may be a simple answer I'm just missing).

David Zemens Over a year ago

@BruceWayne I assumed that is the userform name, since OP uses it with the .Show method (of UserForm object). It wouldn't be declared, it's built in the VBE designer and there you can set it's .Name property.

Collectives™ on Stack Overflow

Need to open an excel file manually in the middle of a macro

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related