Selecting another workbook to import

[bkeat]

Hi

I am a beginner with vba and I am stuck in trying to import another excel workbook with its location being flexible as the file-name will change.


This is what i got so far

Dim selectFile As String




selectFile = Application.GetOpenFilename(FileFilter:="Excel Files, *.xls*" _
, FilterIndex:=1, Title:="Open Excel file" _
, MultiSelect:=False)


If selectFile = "False" Then
MsgBox ("Please Select File!")
End If

Having difficulty in building the next block of code for the import.


I am using excel 2010.

Any help would be most appreciated.

Thanks.

 

[Jon von der Heyden]

Ok so you have the dialog box to get the file name. What do you mean by import? Is it just one sheet? A specific range? Can you please elaborate?

 

[bkeat]

Hi thanks for your reply.

Yes i have a window opening up to select the file. I am trying to import data from one workbook sheet into my master file sheet. I will manipulate the data once in myself through another macro. But as the workbook i am importing is not consistent i want to be able to select the file but the data format in the workbook i am importing from will not change.

What i am struggling with is the code to import the data from that file. I have imported text files and this is working but the code does not work for excel files.


Does this clear it up, apologies for not mentioning this above.

Thanks

 

[Jon von der Heyden]

Hello Mate

Still need more specifics please. What is the sheet name that we need to look at in the external workbook? Or is it just the 1st sheet?
Do you want to import all the data in the sheet (i.e. the used range), or a specific range (e.g. A1:Z1000)?
Where do you want to import it to? I need the sheet name. And will it be appended, or must it overwrite the existing data?

 

[bkeat]

Ok sorry for not being clear I am still a beginner with VBA.

Workbook1 is the data source file i want to import from and Workbook2 is the master file i will be using.

Workbook1 sheet name: "REPORT"

Workbook2 sheet name master file: "Forecast"

It is just the one sheet in the Workbook1 i want to import from and its the only sheet in the Workbook1 and it is all the data as it is in Workbook1 Sheet not any specific range. I will manipulate once i have it in Workbook2.

 

[Jon von der Heyden]

Try;

Public Sub ImportSheetData()
    Dim wkbImportFrom As Excel.Workbook

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

     On Error GoTo err_Catch

    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        With .Filters
            .Clear
            Call .Add(Description:="Excel files", Extensions:="*.xls*", Position:=1)
        End With
        .FilterIndex = 1
        If .Show <> 0 Then
            Set wkbImportFrom = Workbooks.Open(Filename:=.SelectedItems(1), ReadOnly:=True)
        Else
            Call Err.Raise(Number:=1024 + vbObjectError, Description:="No file selected!")
        End If
    End With

    With ThisWorkbook
        Call .Worksheets.Add(Before:=.Sheets(1))
        Call wkbImportFrom.Sheets(1).UsedRange.Copy
        With .Sheets(1).Range("A1")
            Call .PasteSpecial(Paste:=xlValues)
            Call .PasteSpecial(Paste:=xlFormats)
        End With
    End With

    Application.CutCopyMode = False

proc_End:
    If Not wkbImportFrom Is Nothing Then
        Call wkbImportFrom.Close(SaveChanges:=False)
    End If
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    Exit Sub

err_Catch:
    Call MsgBox(Prompt:=Err.Description, Buttons:=vbOKOnly + vbExclamation)
End Sub

 

[bkeat]

Thanks very much this works perfect.

If I wanted the macro to run and load in a specific sheet already created called "Forecast" not create a new one, how could i do this with the code?

Is it here:

With ThisWorkbook
        Call .Worksheets.Add(Before:=.Sheets(1))
        Call wkbImportFrom.Sheets(1).UsedRange.Copy
        With .Sheets(1).Range("A1")
            Call .PasteSpecial(Paste:=xlValues)
            Call .PasteSpecial(Paste:=xlFormats)
        End With
    End With

 

[Jon von der Heyden]

You still need to tell me if it should be appended to 'Forecast', or if it should overwrite the existing data in 'Forecast'

 

[bkeat]

It can overwrite but there will be no data in the sheet it will be run fresh each time.

 

[Jon von der Heyden]

Ok

Swap the bit you extracted for:

    With ThisWorkbook
        Call .Sheets("Forecast").UsedRange.Clear
        Call wkbImportFrom.Sheets(1).UsedRange.Copy
        With .Sheets("Forecast").Range("A1")
            Call .PasteSpecial(Paste:=xlValues)
            Call .PasteSpecial(Paste:=xlFormats)
        End With
    End With