Log In
Register
Home
Power Query
January 30, 2020
Offline
Afternoon All,
Im doing a CustomUI database with DropdownBox to open a folder :H:\PROJECT-OPS\NSW Warehouse\NSWTA 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-OPS\NSW Warehouse\NSWTA Inventory Listing
I attach the program. There is a "Import Data" button to experdite the code
Thanks
Steve
Trusted Members
Moderators
November 1, 2018
Offline
Try adding:
ChDrive "H:"
ChDir "H:\PROJECT-OPS\NSW Warehouse\NSWTA Inventory Listing"
before the line to select the file.
Power Query
January 30, 2020
Offline
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
1 Guest(s)
