VBA code to open a file

[mjhogan2]

Hi,

I m new to VBA coding - I am looking for some guidance to the following:

I would like to use a workbook to search for a folder in explorer, open the desired workbook in that folder, then slect a range of data from the selected workbook and store it in a new work book. I need something that will work on what ever workbook in the file I choose.

I hope this makes sense - I have been trying to find some online tutes online but nothing suits what I need

Cheers,

Matt

 

[jolivanes]

So you just want to open a closed workbook.
You then want to go through possibly many sheets to find the range you want to copy and paste this in a new workbook.
Or is the sheet to copy from and the range to copy known? (hard coded like "Sheets("Sheet3").Range("C12:F25").Copy")

 

[mjhogan2]

Hi,
To clarify - I want to:
1. Open a workbook from a file location (from a number of workbooks stored there)
2. From the selected workbook - import data (from a known range) to new workbook
3. Then I will perform some calculations following on.

So - the sheet in the workbook will aways be sheet 1, and the range will also be known.
I am just trouble importing the data fom the original fileto a new sheet

 

[jolivanes]

Try this.

Sub Open_Workbook_Single_Select()
    Dim myFile As String, wbS As Workbook
    Application.ScreenUpdating = False
    Sheets.Add(, Sheets(Sheets.Count)).Name = "Temp"
    myFile = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
    MultiSelect:=False)
        Set wbS = Workbooks.Open(myFile)
            wbS.Sheets("Sheet1").Range("A3:A30").Copy ThisWorkbook.Sheets("Temp").Range("A1")    '<---- Change the ranges as required
        wbS.Close False
    Application.ScreenUpdating = True
End Sub

 

[jolivanes]

After the line

wbS.Close False

insert these two lines

Sheets("Temp").Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "Temp", FileFormat:=xlOpenXMLWorkbook

This saves the new Workbook as an .xlsx type file in the same Folder where your Workbook that has the code in it is saved.

If you want to save it in .xlsm format, use

FileFormat:=xlOpenXMLWorkbookMacroEnabled