open file by inputting file name in cell?

[Guest]

My colleagues require data from different spread sheets, from specific cells
in those spread sheets. However the data is alway in the same location for
all the spread sheets (A2 to A59). I wish to create a macro that when they
enter the file name in a specific cell and then click the macro button the
following will happen.

The excell spread sheet they have specified will open. The data which is
always in the same place will be cut and pasted in to the existing excell
file on a specific sheet and location. Then this sheet needs to be saved as a
csv file with the file name they where initially openning.

 

[Guest]

Try this code. Instead of entering filename into cell I used
GetOpenFilename. If you need to you can write the filename retuned by this
function into a cell location. Why have a person mis-type a filename when
the pop up window will correctly find the filename.

Sub getdata()

MyPath = ThisWorkbook.Path

filetoopen = Application _
.GetOpenFilename(MyPath & "Excel Files (*.xls), *.xls")
If filetoopen = False Then
MsgBox ("Cannot Open " & filetoopen)
End If

Workbooks.Open filetoopen

'open workbook is now active
'Thsiworkbook is workbook where macro is running
With Sheets("Sheet1")
.Range("A2:A59").Copy _
Destination:=ThisWorkbook.Sheets("Sheet2"). _
Range("A5")
End With

filetosave = Left(filetoopen, _
InStr(filetoopen, ".")) & "CSV"
ActiveWorkbook.SaveAs _
Filename:=filetosave, _
FileFormat:=xlCSV
End Sub