Hi - I have this code for opening a closed workbook, copying data, then closing the workbook. How do i change the fPath to use "\\Server\Folder\SubFolder\SubSubFolder" instead of using a drive letter?
thanks!
Sub Test_GetValues()
Dim fPath As String, fOpen As Variant, wb As Workbook, rng1 As Range, rng2 As Range
fPath = "K:\Folder\Subfolder\SubSubFolder" & Sheets("SheetXXX").Range("A2")'specify default drive and folder for GetOpenFilename
ChDrive Left(fPath, 1)
ChDir fPath
'get workbook
fOpen = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
If fOpen <> False Then Set wb = Workbooks.Open(fOpen) Else GoTo handling
'get ranges
With Application
Set rng1 = .InputBox("Select range to pull", "Get data", , , , , , 8)
ThisWorkbook.Activate
Set rng2 = .InputBox("Select paste cell", "Paste where", , , , , , 8)
End With
'copy\paste values & formats
rng1.Copy
rng2.PasteSpecial (xlPasteFormulasAndNumberFormats)
rng2.PasteSpecial (xlPasteValues)
wb.Close False 'close without saving
Exit Sub
handling:
MsgBox "nothing selected"
End Sub
thanks!
Sub Test_GetValues()
Dim fPath As String, fOpen As Variant, wb As Workbook, rng1 As Range, rng2 As Range
fPath = "K:\Folder\Subfolder\SubSubFolder" & Sheets("SheetXXX").Range("A2")'specify default drive and folder for GetOpenFilename
ChDrive Left(fPath, 1)
ChDir fPath
'get workbook
fOpen = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
If fOpen <> False Then Set wb = Workbooks.Open(fOpen) Else GoTo handling
'get ranges
With Application
Set rng1 = .InputBox("Select range to pull", "Get data", , , , , , 8)
ThisWorkbook.Activate
Set rng2 = .InputBox("Select paste cell", "Paste where", , , , , , 8)
End With
'copy\paste values & formats
rng1.Copy
rng2.PasteSpecial (xlPasteFormulasAndNumberFormats)
rng2.PasteSpecial (xlPasteValues)
wb.Close False 'close without saving
Exit Sub
handling:
MsgBox "nothing selected"
End Sub