98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » How to open an xls file via VBA, allowing the user to choose a f
How to open an xls file via VBA, allowing the user to choose a f
Resolved · Urgent Priority · Version 2010
Tue 2 Sep 2014, 13:15SAM (guest) said...
How to open an xls file via VBA, allowing the user to choose a f
Hi
Please can you kindly advise on the VBA code to Open a file (allowing the user to choose the directory and file path as it may be different each time). Then copy the data from a particular sheet within that file and then paste it into a master sheet and file.
I had previously used a connection source for txt and then used VBA to refresh this link but the user does not want to use csv or txt rather just use the source xls file. I cannot see or know a way to do this.
Thanks
Wed 3 Sep 2014, 12:54Trainer Jens said...
RE: How to open an xls file via VBA, allowing the user to choose
Hi Sam,
Thanks for the forum question.
The code below is for selecting the text file. I do not know how you have the data in the master sheet. If you want to add the data starting from the first blank row you will need a variable to store the information in the memory.
I hope the code will help you in the right direction.
Sub SelectFiles()
Dim iFileSelect As FileDialog
Set iFileSelect = Application.FileDialog(msoFileDialogOpen)
With iFileSelect
.AllowMultiSelect = True 'false if you only want to select one file
.Title = "Select Text Files"
.Filters.Clear
.Filters.Add "text Files", "*.*"
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then
Dim vrtSelectedItem
For Each vrtSelectedItem In iFileSelect.SelectedItems
'enter the code for copy and paste here
Next vrtSelectedItem
End If
End With
Set iFileSelect = Nothing
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 5 Sep 2014, 12:00SAM (guest) said...
RE: How to open an xls file via VBA, allowing the user to choose
Hi
Thank you for your time and help.
However as mentioned previously the user does not want to use txt file. so just XLS file format.
The steps would be from the user:
- Open the Rec file from which two sheets from separate files data needs to be copied into this master file under the applicable sheet name -SheetP and SheetD
- Following have Rec file open, the user would run a macro to do:
-User has the ability to Open a file (file A) from a directory, find the xls file originally saved down (this would be a different place each month), go to for example Sheet 1 and copy the data and paste this into the Rec file under SheetP.
Then close this File A.
-User has the ability to Open a file (file B) from a directory, find the xls file originally saved down (this would be a different place each month), go to for example Sheet 2 and copy the data and paste this into the Rec file under SheetD.
Then close this File B.
So in both cases the user would get the directory window open to choose the file and from which file path, these files would be closed after once the data has been copied into the master Rec file. The file name of File A and B would be different each time so I cannot have it as a set directory name.
I hope thats clearer, sorry if I was not explicit enough before.
Thanks again.
S.A.M
Fri 19 Sep 2014, 08:32Trainer Doug said...
RE: How to open an xls file via VBA, allowing the user to choose
Hi Shakoora.
Sorry for the delay with replying to your question. The trainer assigned to this is training today. However, he is free on Monday and will look into it then. It might help him to send the code you have already which opens the Excel file and the layout of one of the master sheets.
The Forum is designed to answer questions that relate to the Microsoft Office training courses and not to write vba code but we will help if we can.
Regards
Doug Dunn
Best STL
Fri 10 Oct 2014, 12:23Trainer Doug said...
RE: How to open an xls file via VBA, allowing the user to choose
Hi Shakoora.
Good to speak with you and glad to hear you found a solution to your query.
As you wanted to see a second solution here is the code for 2 macros that copy data from an open workbook to SheetP or SheetD in the File Ref master.
See the files attached if you want to see how it works.
Sub CopyDataP()
'Open Rec File.xlm
'Then 'TestCopyData.xlsx
Dim DataFile As String
Application.DisplayAlerts = False
DataFile = ActiveWorkbook.Name
Windows(DataFile).Activate
Sheets("Sheet1").Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Windows("Rec File.xlsm").Activate
Sheets("SheetP").Select
Range("A1").Select
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Windows(DataFile).Activate
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub
Sub CopyDataD()
'Open Rec File.xlm
'Then 'TestCopyData2.xlsx
Dim DataFile As String
Application.DisplayAlerts = False
DataFile = ActiveWorkbook.Name
Windows(DataFile).Activate
Sheets("Sheet1").Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Windows("Rec File.xlsm").Activate
Sheets("SheetD").Select
Range("A1").Select
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Windows(DataFile).Activate
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub
Hope that helps sorry again for the delay in responding.
You are welcome to send more posts.
Regards
Doug
Best STL
Attached files...
TestCopyData.xlsxFri 17 Oct 2014: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Select Single Data MarkerTo select a single data marker in a chart, ie line, bar or column; |
