-1

I have three files one is a excel file enabled with macro where my macro is(1), the csv file to run the macro on(2). The new csv file that would be opened(3)

I am new to userform I created a web browser control and was able to initialize in the userform and added the code

  Private Sub UserForm_Initialize()
  Me.WebBrowser1.Navigate "http://sharepoint_site.aspx"   
  End Sub

now when I click on the required csv file I get file download. There how do I just open the file and make this newly opened csv file as active? There are many csv files on the sharepoint site.The user selects a specific file and gets a file download box where it should jusst open that csv file. The reason for using userform as suggested by @David was to better control the newly opened CSV file and have the name of the file stored to perform the next steps of the code rather than file 2 where the macro is run to be the active workbook.Below code was my previous code which was part of a case statement.

Dim IE As Object
Set IE = CreateObject("InternetExplorer.application")
With IE
    .Visible = True
   .navigate ("https://site.aspx")
  MsgBox "Select the file and click open file"

Here obviously activated the file (2) where the macro ran but wanted to activate the newly opened file. Any help on this is greatly appreciated & thank you in advance.

Community
  • 1
  • 1
viji
  • 425
  • 2
  • 6
  • 16
  • @ I referred http://stackoverflow.com/questions/30765575/vba-activate-open-file?rq=1 but I dont think I can use this because I am unaware of the path. – viji Sep 04 '15 at 18:18
  • This is part of the problem with relying on `ActiveWorkbook` ([generally recommended to avoid this method](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros)). Note also that the when the user closes the MsgBox, the remaining code (`lrow = ...`) will execute. The user doesn't have any chance to open the SharePoint file until *after* the code has executed. – David Zemens Sep 08 '15 at 19:24
  • One way to control this would be to open the SharePoint site in a WebBrowser control on a UserForm, and then you can properly scope the selected workbook file to a variable, and you will have better ability to control what happens to it, without relying on `ActiveWorkbook`. – David Zemens Sep 08 '15 at 19:25
  • OK. so, do you have further questions? If so, you should revise your original question (above) to include the updated details, and the updated code which you are currently using. – David Zemens Sep 09 '15 at 14:37
  • I just revised the question – viji Sep 10 '15 at 18:34

1 Answers1

0

Try something like this instead of the WebBrowser control, my apologies for pointing you in the wrong direction:

Sub foo()
Dim SummaryWB As Workbook
Dim vrtSelectedItem As Variant

With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = "https:\\your_sharepoint\team\folder" & "\"
    .AllowMultiSelect = False
    .Show
    For Each vrtSelectedItem In .SelectedItems
        Set SummaryWB = Workbooks.Open(vrtSelectedItem)
    Next
End With

If SummaryWB Is Nothing Then Exit Sub

Call SomeOtherMacro(SummaryWB)

End Sub

So then you have some other macro that will process this workbook, you send the workbook to it like the above Call statement, and make sure that the other procedure accepts a Workbook:

Sub SomeOtherMacro(wb as Workbook)

' This macro will do something to the workbook
    wb.Worksheets(1).Select
    MsgBox wb.Name & " sheet 1 is now selected!"
End Sub

Modified from this answer.

Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Does this work for a sharepoint site? I hope I made it clear that it is from a website that I want to open CSV file. – viji Sep 10 '15 at 19:48
  • Oh yah this is only for SharePoint... not sure is possible with the webbrowser control, I tried a few things earlier today but couldn't get it to work. It may be possible with IE or Selenium Web Driver but Might be complicsted.... – David Zemens Sep 10 '15 at 19:54
  • Viji, your question **specifically** says you are trying to download from a Sharepoint site. Why does the solution posted below not work for you? Did you even try it? – David Zemens Sep 11 '15 at 20:11
  • I tried this solution but got the error like Case without select as this is part of one of the case. The error may be due to my code after that which I have performed but why cant I just use this directly after I download the file. `csvFileName = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Select a CSV File", MultiSelect:=False) If csvFileName = False Then Exit Sub Application.Workbooks.Open (csvFileName)` Is there any advantage of using your code? Please suggest – viji Sep 14 '15 at 18:35
  • No, the error is not due to my code for the simple fact that there is no `Select` statement in my code. If you are getting an error, please consider revising your original question to include the code you are currently using, and indicate at which line the error occurs. – David Zemens Sep 14 '15 at 18:37