leal72
02-16-2017, 09:01 AM
I wanted to modify this code so that the Get Open Filename opens a specific folder. This folder resides on our company Sharepoint or Intranet. I've tried ChDir but that did not work.
Sub OpenWebFolder()
Dim VPSbook As Workbook
Dim PSummary As Worksheet
Dim PRequest As Worksheet
Dim MyFiles As Variant
Dim lastROW As Long
Dim WKbook As Workbook
Dim RequestSheet As Variant
MyFiles = Application.GetOpenFilename _
("Excel Files (*.xls*), *.xls*", _
Title:="Select run(s) to Summarize", MultiSelect:=True)
' Exit if dialog box canceled
If Not IsArray(MyFiles) Then
MsgBox "No file was selected."
Exit Sub
End If
Set VPSbook = Workbooks("Summary Workbook") ' Summary workbook
Set PSummary = VPSbook.Sheets("Info") ' Summary sheet/tab
lastROW = PSummary.Cells(Rows.Count, 1).End(xlUp).Row 'find last used row
Application.ScreenUpdating = False
For Each RequestSheet In MyFiles
Set WKbook = Workbooks.Open(Filename:=RequestSheet, ReadOnly:=True) ' Production Workbook
Set PRequest = WKbook.Sheets("production info") ' Production Request
' Do stuff here
WKbook.Close
lastROW = lastROW + 1
Next RequestSheet
End Sub
Sub OpenWebFolder()
Dim VPSbook As Workbook
Dim PSummary As Worksheet
Dim PRequest As Worksheet
Dim MyFiles As Variant
Dim lastROW As Long
Dim WKbook As Workbook
Dim RequestSheet As Variant
MyFiles = Application.GetOpenFilename _
("Excel Files (*.xls*), *.xls*", _
Title:="Select run(s) to Summarize", MultiSelect:=True)
' Exit if dialog box canceled
If Not IsArray(MyFiles) Then
MsgBox "No file was selected."
Exit Sub
End If
Set VPSbook = Workbooks("Summary Workbook") ' Summary workbook
Set PSummary = VPSbook.Sheets("Info") ' Summary sheet/tab
lastROW = PSummary.Cells(Rows.Count, 1).End(xlUp).Row 'find last used row
Application.ScreenUpdating = False
For Each RequestSheet In MyFiles
Set WKbook = Workbooks.Open(Filename:=RequestSheet, ReadOnly:=True) ' Production Workbook
Set PRequest = WKbook.Sheets("production info") ' Production Request
' Do stuff here
WKbook.Close
lastROW = lastROW + 1
Next RequestSheet
End Sub