PDA

View Full Version : VBA Script to import Sheet content from external workbook



iflaneur
10-03-2013, 04:22 AM
Hey guys,

I want to use the script below to copy:
- the content of Sheet "RAWDATA" in file C:\Client Reports\Delivery.xls
- into Sheet "RAWDATAClient" in the active workbook


but don't know where to specify the source file and the destination sheet
Can you help me?


Sub ImportRangeFromWB(SourceFile As String, SourceSheet As String, _
SourceAddress As String, PasteValuesOnly As Boolean, _
TargetWB As String, TargetWS As String, TargetAddress As String)
' Imports the data in Workbooks(SourceFile).Worksheets(SourceSheet).Range(SourceAddress)
' to Workbooks(TargetWB).Worksheets(TargetWS).Range(TargetAddress)
' Replaces existing data in Workbooks(TargetWB).Worksheets(TargetWS)
' without prompting for confirmation
' Example
' ImportRangeFromWB "C:\FolderName\TargetWB.xls", _
"Sheet1", "A1:E21", True, _
ThisWorkbook.Name, "ImportSheet", "A3"

Dim SourceWB As Workbook, SourceWS As String, SourceRange As Range
Dim TargetRange As Range, A As Integer, tString As String
Dim r As Long, c As Integer
' validate the input data if necessary
If Dir(SourceFile) = "" Then Exit Sub ' SourceFile doesn't exist
Set SourceWB = Workbooks.Open(SourceFile, True, True)
Application.StatusBar = "Reading data from " & SourceFile
Application.ScreenUpdating = False ' turn off the screen updating
Workbooks(TargetWB).Activate
Worksheets(TargetWS).Activate

' perform import
Set TargetRange = Range(TargetAddress).Cells(1, 1)
Set SourceRange = SourceWB.Worksheets(SourceSheet).Range(SourceAddress)
For A = 1 To SourceRange.Areas.Count
SourceRange.Areas(A).Copy
If PasteValuesOnly Then
TargetRange.PasteSpecial xlPasteValues
TargetRange.PasteSpecial xlPasteFormats
Else
TargetRange.PasteSpecial xlPasteAll
End If
Application.CutCopyMode = False
If SourceRange.Areas.Count > 1 Then
Set TargetRange = _
TargetRange.Offset(SourceRange.Areas(A).Rows.Count, 0)
End If
Next A

' clean up
Set SourceRange = Nothing
Set TargetRange = Nothing
Range(TargetAddress).Cells(1, 1).Select
SourceWB.Close False
Set SourceWB = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

Thank you very much!
Daniele

snb
10-03-2013, 04:56 AM
Sub M_snb()
With ThisWorkbook.Sheets("RAWDATACLIENT").QueryTables.Add("ODBC;DSN=Excel files;DBQ=C:\Client Reports\Delivery.xls", Sheets("RAWDATACLIENT").Range("A1"))
.CommandText = "SELECT * FROM `Rawdata$`"
.Refresh False
End With
End Sub

Kenneth Hobs
10-03-2013, 07:09 AM
From the comment, was it not clear? OF course the input parameters make it clear as well.


' ImportRangeFromWB "C:\FolderName\TargetWB.xls", _ "Sheet1", "A1:E21", True, _ ThisWorkbook.Name, "ImportSheet", "A3"

You would use it like this:

Sub Test
ImportRangeFromWB "C:\FolderName\TargetWB.xls", "Sheet1", "A1:E21", True, ThisWorkbook.Name, "ImportSheet", "A3"
End Sub

Their choice of the name for the source workbook may have confused you. Instead of TargetWB.xls, it should have been SourceWB.xls for example. The input parameter order governs if the input parameter labels are not used.

IF the code is in some other workbook, just change ThisWorkbook to ActiveWorkbook.

As always, test these sorts of things on a backup copy.

iflaneur
10-03-2013, 07:51 AM
Thanks Kenneth - actually when I try to use ActiveWorkbook I get a run-tine error 438 (object doesn't support this property or method).

Also, how would I set Range(SourceAddress) in this example "A1:E21" as an open range to include all populated cells in "Sheet1"?

Sorry I'm a bit of a nube - working on my VBA skills as work demands.

Thanks ,
Daniele

Kenneth Hobs
10-03-2013, 09:00 AM
If you don't have the set range address or an extra large range address to make sure you get all the usedrange in a worksheet, use code like snb's.

There is one other method to do this sort of thing using R1C1 syntax to import all values.