Forum

GetOpenFileName ope...
 
Notifications
Clear all

GetOpenFileName open specifc folder

3 Posts
2 Users
0 Reactions
403 Views
(@stevenbehr1)
Posts: 92
Estimable Member
Topic starter
 

Afternoon All,

Im doing a CustomUI database with DropdownBox to open a folder :H:PROJECT-OPSNSW WarehouseNSWTA Inventory Listing 

When I select a excel file it copies the data to the Tab InventoryListing . Range A10 (Which it does)

But what this is code is doing is opening C:

Option Explicit

Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Application.ScreenUpdating = False

FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Sheets(1).Range("A1:E20").Copy
ThisWorkbook.Worksheets("InventoryListing").Range("A10").PasteSpecial xlPasteValues
OpenBook.Close False

End If
Application.ScreenUpdating = True
End Sub

Is there any way to tell it to open from H:PROJECT-OPSNSW WarehouseNSWTA Inventory Listing

 

I attach the program. There is a "Import Data" button to experdite the code

 

Thanks

 

Steve

 
Posted : 20/09/2021 11:55 pm
(@debaser)
Posts: 836
Member Moderator
 

Try adding:

 

ChDrive "H:"

ChDir "H:PROJECT-OPSNSW WarehouseNSWTA Inventory Listing"

 

before the line to select the file.

 
Posted : 21/09/2021 4:42 am
(@stevenbehr1)
Posts: 92
Estimable Member
Topic starter
 

Thanks Velouria,

 

That sort of works!

But I had another idea

Sub Get_Data_From_File1()
Dim sFolder As String
Dim OpenBook As Workbook
' Open the select folder prompt
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = "C:Maroubra NSW TA"
If .Show = -1 Then ' if OK is pressed
sFolder = .SelectedItems(1)
End If
End With
Application.ScreenUpdating = False
'If sFolder <> False Then
Set OpenBook = Application.Workbooks.Open(sFolder)
OpenBook.Sheets(1).Range("A1:I2000").Copy
ThisWorkbook.Worksheets("SelectFile").Range("A10").PasteSpecial xlPasteValues

Application.CutCopyMode = False

OpenBook.Close False

'End If
'Application.ScreenUpdating = True
End Sub

 

This does work to well!

 

but thanks for that idea

 

Steve

 
Posted : 21/09/2021 5:36 am
Share: