VBA Macro - Import data from different excel file for a given value

[dan4y]

Hello guys,
I am very unprepared in everything that means VBA Macros but still I have to find a solution to make my work easier. :banghead:
If you don't mind, I'd like to share with you where I got stuck.

In one file I need some data from another file but not all the lines.

In the file where I have to get my data from, I have the following columns:




row_no.
task
user_id
upload_date
week_no.
year
asin
Marketplace
Keywords_for_upload
GL
PL




I would like, if possible, to import my lines for a value of the "week_no" column that I can add next to the button.

That's what I've managed to do so far, only I can't handle the condition for that "range":

Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Application.ScreenUpdating = False
FileToOpen = "C:\Source.xlsx"
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Sheets(1).Range("B1:K52").Copy
ThisWorkbook.Worksheets(2).Range("A1").PasteSpecial xlPasteValues
OpenBook.Close False
End If
Application.ScreenUpdating = True
End Sub



I hope I was quite explicit and thank you in advance for your help.

[Paul_Hossler]

It's usually better to include sample attachments so people don't have to make some up

Use this as as starting point. My test data is attached



Option Explicit


Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim rData As Range
Dim iRow As Long, iWeek As Long, iOut As Long


FileToOpen = Application.GetOpenFilename("Data File (*.xlsx), *.xlsx")
If FileToOpen = False Then Exit Sub

iWeek = Application.InputBox("What Week?", "Get the Week Number", 0, , , , , 1)
If iWeek < 1 Then Exit Sub

Application.ScreenUpdating = False

Set OpenBook = Application.Workbooks.Open(FileToOpen)

iOut = 2

With OpenBook.Sheets(1)
For iRow = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
If .Cells(iRow, 5).Value = iWeek Then
.Cells(iRow, 1).Resize(1, 11).Copy ThisWorkbook.Sheets(1).Cells(iOut, 1)
iOut = iOut + 1
End If
Next iRow
End With

OpenBook.Close False

Application.ScreenUpdating = True
End Sub

[dan4y]

It's usually better to include sample attachments so people don't have to make some up

Use this as as starting point. My test data is attached



Option Explicit


Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim rData As Range
Dim iRow As Long, iWeek As Long, iOut As Long


FileToOpen = Application.GetOpenFilename("Data File (*.xlsx), *.xlsx")
If FileToOpen = False Then Exit Sub

iWeek = Application.InputBox("What Week?", "Get the Week Number", 0, , , , , 1)
If iWeek < 1 Then Exit Sub

Application.ScreenUpdating = False

Set OpenBook = Application.Workbooks.Open(FileToOpen)

iOut = 2

With OpenBook.Sheets(1)
For iRow = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
If .Cells(iRow, 5).Value = iWeek Then
.Cells(iRow, 1).Resize(1, 11).Copy ThisWorkbook.Sheets(1).Cells(iOut, 1)
iOut = iOut + 1
End If
Next iRow
End With

OpenBook.Close False

Application.ScreenUpdating = True
End Sub




It works perfectly! Thanks for helping me!

[snb]

A oneliner suffices

Sub M_snb()
If Dir("C:\Source.xlsx")<>"" then Thisworkbook.sheets(1).cells(2,1).resize(52)=getobject("C:\Source.xlsx").sheets(1).range("B2:B52").offset(inputbox("weeknumber")-1).value
End Sub