Excel VBA Import data

[themluis]

Hey guys I nearly don't know nothing about Excel VBA and I can't do a code that would get a data from a file that my company has(yes this is the task my company gave me knowing I don't have knowledge on it) given me, so it needs to copy three columns and copy onto my file.

If you guys could help out that would be great, I have a short scheduled of delivery, and I'm not being able to alter other codes of importing data into my Excel File, because I'm a newbie.

Any information needed tell me, and I will post as soon as possible.

 

[Juanca1080]

Hi,
You could try this, adjusting the lines I comment to fit your needs.

Option Explicit
Sub ImportData()
Application.ScreenUpdating = False
Dim Path As String
Dim SourceWb As Workbook
Dim TargetWb As Workbook
Path = "C:\Users\User\Desktop\Source.xlsm" [B]'Change this to your company workbook path[/B]
Workbooks.Open (Path)
Set SourceWb = Workbooks("Source") [B]'Change "Source" to the name of your company workbook[/B]
Set TargetWb = ThisWorkbook
SourceWb.Sheets(1).Columns("A:C").Copy Destination:=TargetWb.Sheets(1).Range("A1") [B]'Change the ranges to copy and destination to fit your needs[/B]
SourceWb.Close savechanges:=False
Application.ScreenUpdating = True
End Sub

Hope it helps.

 

[shabbaranks]

Hi,

Just looking at this its pretty much exactly what I need BUT is there a way to automatically import the worksheet upon opening Excel? Also is it possible to hide the information imported? As Im trying to create a drop down list but don't want the imported info visible on the additional sheet.

Thanks

 

[Jorge_B]

Hi shabbaranks,
I think you can accomplish what you want by making a few adjustments to this procedure.
To get the macro running upon workbook opening you just have to paste the code in “ThisWorkbook” module instead of a standard module.
So here’s the code:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim Path As String
Dim SourceWb As Workbook
Path = "C:\Users\UserName\Desktop\Source.xlsx" 'Change to the source workbook path
Workbooks.Open (Path)
Set SourceWb = Workbooks("Source") 'Change "Source" to the name of the source workbook
SourceWb.Sheets(1).UsedRange.Copy Destination:=ThisWorkbook.Sheets(1).Range("A1") 'Change this to fit your needs
SourceWb.Close savechanges:=False
ThisWorkbook.Sheets(1).Visible = False
Application.ScreenUpdating = True
End Sub

Hope it helps.