Import excel sheet using VBA

[sasi]

How can i import Excel file(A.xls) to another excel file (B.xls) using VBA,I have the tool in Excel for comparing sheet1 and Sheet2 , every time i need to copy and paste the sheet1 and sheet2 values from some other excel files. can any one provide the macro to import the excel files.Thanks

 

[Misca]

Maybe something like:

Sub CopySheets()
    
Dim WB As Workbook
Dim SourceWB As Workbook
Dim WS As Worksheet
Dim ASheet As Worksheet

'Turns off screenupdating and events:
Application.ScreenUpdating = False
Application.EnableEvents = False

'Sets the variables:
Set WB = ActiveWorkbook
Set ASheet = ActiveSheet
Set SourceWB = Workbooks.Open(WB.Path & "\MyOtherWorkbook.xls")  'Modify to match

'Copies each sheet of the SourceWB to the end of original wb:
For Each WS In SourceWB.Worksheets
    WS.Copy after:=WB.Sheets(WB.Sheets.Count)
Next WS
    
    SourceWB.Close savechanges:=False
    Set WS = Nothing
    Set SourceWB = Nothing
    
WB.Activate
ASheet.Select
    Set ASheet = Nothing
    Set WB = Nothing
    
Application.EnableEvents = True
    
End Sub

The code above copies each worksheet of the "SourceWB" at the end of current workbook.

 

[sasi]

Is it not possible to select the excel required File from folder and import data into excel sheet like we do importing text(.txt) file into excel sheet

 

[Misca]

Sure you can do that. Here's how: http://www.tek-tips.com/faqs.cfm?fid=4114

What you need to do is replace the "Set SourceWB" -line in my code with either one of the codes on the sheet.

You can't use the codes as they are but you need to modify them a bit: The "Workbooks.Open" -lines of the code is the one that you're after (and you need to add the "Set SourceWB=" -part to that line if you want to use the rest of my code with that) but you need the lines before that to tell Excel the name of the folder you want it to open + what kind of files you're interested in.

Try F8 through the codes as they are to see what each line does so you know what you need to change.

 

[sasi]

Thanks Misca,provide information helped me to acheive.

Sub OpenSingleFile()
Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Filename As Variant
On Error Resume Next
Filter = "Excel Files (*.xls),*.xls,"
FilterIndex = 3
Title = "Select a File to Open"
ChDrive ("E")
ChDir ("E:\excel")
With Application
Filename = .GetOpenFilename(Filter, FilterIndex, Title)
ChDrive (Left(.DefaultFilePath, 1))
 ChDir (.DefaultFilePath)
End With
If Filename = False Then
MsgBox "No file was selected."
Exit Sub
End If
ImportThisOne CStr(Filename)
End Sub
Sub ImportThisOne(sFileName As String)
On Error Resume Next
   Dim oBook As Workbook
   Workbooks.Open sFileName
   Set oBook = ActiveWorkbook
   'Now do your processing on the newly imported sheet
   On Error Resume Next
   'Copy new sheet into this workbook
   oBook.Worksheets(Sheet1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
   'close text file, do not save changes
   'oBook.Close False
   'Set oBook = Nothing
End Sub

 

[Xander42]

I used this code to import three sheets into a workbook, which worked OK.
However, the imported sheets contain formulas that refer to each other imported sheets. When imported these formulas reference the original sheet (in "MyOtherWorkbook.xls in your example) - NOT the copied sheet in the destination workbook.
How do I fix this? - I want the formulas to refer to the local copies of the sheets, not the originals.

 

[Xander42]

Maybe something like:
...
The code above copies each worksheet of the "SourceWB" at the end of current workbook.

I used this code to import three sheets into a workbook, which worked OK.
However, the imported sheets contain formulas that refer to each other imported sheets. When imported these formulas reference the original sheet (in "MyOtherWorkbook.xls in your example) - NOT the copied sheet in the destination workbook.
How do I fix this? - I want the formulas to refer to the local copies of the sheets, not the originals.