VBA Script to import Sheet content from external workbook

iflaneur

New member
Joined
Oct 3, 2013
Messages
1
Reaction score
0
Points
0
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?






Code:
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

Cross-posted here.
 
Last edited by a moderator:

pmyk

New member
Joined
Oct 15, 2013
Messages
14
Reaction score
0
Points
0
Since no one has replied, I am trying to reply your query.
I am still learning. You seem to know more than I know.
Yet, try this:
Code:
Private Sub CommandButton2_Click()
 Call ImportRangeFromWB
End Sub


Private Sub ImportRangeFromWB()
 Dim book1 As Workbook
 Dim book2 As Workbook
 Dim book2Name As String
 book2Name = "Delivery.xls"
 Dim book2NamePath As String
 book2NamePath = "C:\Client Reports\" & book2Name
 Set book1 = Workbooks("SourceFile.xls")
 Set myData = Workbooks.Open(book2NamePath)
 Set book2 = Workbooks(book2Name)
 'Clear Contents to show just new search data
 book2.Sheets(1).Cells.ClearContents
 Dim LstRow As Long
 Dim RowCtr As Long
 LstRow = book1.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 'to go to last data cell inspite of blank cells.  Counts Lat Row that has data in this column.
 RowCtr = 1
 Do
  RowCtr = RowCtr + 1
  'pull data from Book1 and paste data in Book2
  'RowCtr=Row to pull/paste data. 1=Col to pull/paste data
  book2.Sheets(1).Cells(RowCtr, 1) = book1.Sheets(1).Cells(RowCtr, 1) 'Col A
  book2.Sheets(1).Cells(RowCtr, 2) = book1.Sheets(1).Cells(RowCtr, 2) 'Col B
  book2.Sheets(1).Cells(RowCtr, 3) = book1.Sheets(1).Cells(RowCtr, 3) 'Col C
  'here you add more columns
 Loop While RowCtr < LstRow 'will stop if RowCtr > LstRow
 myData.Save
End Sub
Hope this helps. Someone may have a better suggestion.
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
Well, the instruction and an example are given at the beginning of the sub. The problem is not knowing the full extent of the source range before opening the file, so a slight alteration to the original sub:
replace:
Code:
Set SourceRange = SourceWB.Worksheets(SourceSheet).Range(SourceAddress)
with:
Code:
If SourceAddress = "" Then
  Set SourceRange = SourceWB.Worksheets(SourceSheet).UsedRange
Else
  Set SourceRange = SourceWB.Worksheets(SourceSheet).Range(SourceAddress)
End If
Call the sub with:
Code:
ImportRangeFromWB "C:\Client Reports\Delivery.xls", "RAWDATA", "", False, ActiveWorkbook.Name, "RAWDATAClient", "A1"
All untested!
 
Top