PDA

View Full Version : VBA Importing all sheet data from a closed excel workbook



jmaocubo
03-09-2012, 10:06 AM
I was looking at the site for a way to import data from a file to my master file.

I found one that may be indicated (author:Shrivallabha): http://www.vbaexpress.com/forum/showthread.php?t=36528

Sub ImportSheet()
Dim sImportFile As String, sFile As String
Dim sThisBk As Workbook
Dim vfilename As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set sThisBk = ActiveWorkbook
sImportFile = Application.GetOpenFilename( _
FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook")
If sImportFile = "False" Then
MsgBox "No File Selected!"
Exit Sub

Else
vfilename = Split(sImportFile, "\")
sFile = vfilename(UBound(vfilename))
Application.Workbooks.Open Filename:=sImportFile

Set wbBk = Workbooks(sFile)
With wbBk
If SheetExists("Raw_Data") Then
Set wsSht = .Sheets("Raw_Data")
wsSht.Copy before:=sThisBk.Sheets("Sheet1")
Else
MsgBox "There is no sheet with name :Raw_Data in:" & vbCr & .Name
End If
wbBk.Close SaveChanges:=False
End With
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Private Function SheetExists(sWSName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets(sWSName)
If Not ws Is Nothing Then SheetExists = True
End Function

But the code copies the sheet and i need that he copies only the values (range) instead of copying the sheet.
I need all the data from all sheets (same in both files, with the exception of the sheet "macros" that only exists on the master).
Another point is that the file to import is password protected.

Can you help me?

Thanks in advance

Miguel