PDA

View Full Version : Specify which folder to open when using Get Open Filename



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

p45cal
02-16-2017, 06:48 PM
You'll probably have to use ChDrive before using ChDir since ChDir only changes the default directory and not the default drive.