How to get file path from different sheet in VBA

[Amar321]

Hello,
I am new in VBA. I have two sheets i.e. sheet1 has path of input Directory and Output directory.
On sheet2 i am started to code. i wants to read data from multiple xls files that are are stores in a folder . this folder path is specified in sheet1.
and i wants to generate new workbook and copy some data from sheet2 to new workbook and store the file at ouput directory that is specified in sheet1.
Below i have given sheet1
Sheet1.JPG

[dflak]

Do you want to read the input directory and the output directory into the code? If so, this is how it is done.

First, make the very first line in your module "Option Explicit.". (Without the quotes). What this forces you to do is to declare all your variables. It is a good practice to get into and when you type AI when you meant to type the real variable name, A1, Option Explicit will pick it up as an error.

Assuming you declared input_directory and output_directory as strings in your Dim statements ...

Please Login or Register  to view this content.

[Amar321]

Its not working . Below is my code



Public Sub CommandButton1_Click()
'DECLARE AND SET VARIABLES
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Dim mainwb As Workbook
Dim ws As Worksheet
Dim input_directory As String

Dim search_result As Range 'range search result
Dim blank_cell As Long
Dim wb As Workbook
Workbooks("aaa.xlsm").Activate
Set wb = ActiveWorkbook
'Path = "C:\Test\Input\"
input_directory = Dir(Sheets("Sheet1").Range("B2").Value & "*.xls")
Filename = input_directory

'--------------------------------------------
'OPEN EXCEL FILES
Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN
Set wbk = Workbooks.Open(Path & Filename)
'MySheet = Application.Caller.Worksheet.Name
'Set sh = MySheet()
'Variable = ActiveSheet.Name
' Sheets(Variable).Range("A1:D1").Copy
'Sheets("Sheet2").Column(B2).Select.Activate.Paste
' Sheets("Sheet2").Active
'Columns("B2").Select
Set wbk = ActiveWorkbook
Variable = ActiveSheet.Name
wbk.Sheets(Variable).Rows(1).EntireRow.Copy

Workbooks("aaa.xlsm").Activate
' Workbooks("DFT Tool.xlsm").Sheets("Sheet2").Activate
'ActiveWorkbook.ActiveSheet

'Sheets("Sheet2").Activate
'ActiveSheet.Columns("E").Select
' Range("E1").End(xlDown).Offset(1, 0).Select
'ActiveSheet.Paste

Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet2")
For Each cell In ws.Columns(3).Cells
If IsEmpty(cell) = True Then cell.Select: Exit For
Next cell
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True

' Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
wbk.Close savechanges:=False
Filename = Dir
Loop
End Sub

[Doc.AElstein]

Amar321
Hi
Firstly
Please help us to help you:
_A) Please use Code tags around code see Rule 3 of our Forum RULES here .
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] makes your code much easier to read and copy for testing, it also maintains VBA formatting, ( Indents, multiple spaces etc.. )
Highlight your code and click the # icon at the top of your post window.
Practice here: ( Start a New Thread - call it something like "Just Testieing" )
http://www.excelforum.com/development-testing-forum/
More information about these and other tags can be found here
( BTW_.... Here is a pretty color ADD-In Alternative for Posting Code in a Forum post
http://www.excelforum.com/showthread...t=#post4420197 _.... )


_B) Please avoid using Images to show a spreadsheet Range.
Please
Either:
Post screenshots COPYABLE to a Spreadsheet: – See my signature below,
or here:
http://www.excelforum.com/excel-prog...ml#post4399581
Practice here:
http://www.excelforum.com/development-testing-forum/

OR:
Upload a Workbook: See here:
http://www.excelforum.com/the-water-...his-forum.html
http://www.excelforum.com/suggestion...ml#post4335120
OR:
There is also the file share site option such as this free thing:
https://app.box.com/signup/personal
(_ .Remember to select Share after uploading and give us the link they provide if you use that )
But Note: a lot of people can’t / won’t download a File from an external site. So this method is not preferred here
For security reasons and to increase your chances of a response, It is preferable to upload a File with no codes in it and post any codes in the Thread ( using Code Tags # )
Practice here:
http://www.excelforum.com/development-testing-forum/


Finally:
_C) Please take some time to read the Rules and Posting Guidelines
http://www.excelforum.com/forum-rule...rum-rules.html
http://www.excelforum.com/forums-rules/

Thanks





_..........................
So, now back to your problem:

I made some changes to your code and it appears to “work”
Here some general remarks to the changes I made

_ 1) You have not taken dflak’s advice to use Option Explicit and some of your variables are not defined. So at line 300 I have
300 Dim Variable As String
And at line 440 I have
440 Dim cell As Range

_ 2) You were not quite using the correct syntax to get Dir(____) to return your required File Name
Lines 150 – 160 do it correctly using the way suggested by dflak
150 Let input_directory = Sheets("Sheet1").Range("B2").Value
160 Filename = Dir(input_directory & "\" & "*.xls")

_3) Line 210 is modified appropriately to open the selected file

_4) I think you can only Select on the Active sheet, so some modifications were needed around Lines 460 – 500

_........................
So I tested the modified code thus:

I made a file
“aaa.xlsm”
And I saved it in a Folder on my Desktop with Path
C:\Users\Elston\Desktop\petra ernährung ab juli\Juni2016
In sheet 1 of that File I put in that Folder path
Using Excel 2007 32 bit

Row\Col	A	B	C
1
2		C:\Users\Elston\Desktop\petra ernährung ab juli\Juni2016
3

Sheet1

I made a second file,
ProAktuelle01.06.2016.xls
And put something in the first two cells in the first sheet, thus.

Row\Col	A	B	C
1	SomefinkInA1	SomefinkInB1
2

Tabelle1

After running my modified code you get this in Sheet2 of File
“aaa.xlsm”

Row\Col	B	C	D
1		SomefinkInA1
2		SomefinkInB1
3
4

Sheet2

[Doc.AElstein]

Please Login or Register  to view this content.

[Amar321]

Thank you for answer

[Doc.AElstein]

Thank you for answer

You are welcome, let us know please how you get on
Alan