VBA Import cells from a closed workbook

[Dan5977]

Hi all,

I have spent hours searching through past threads to try and come up with a solution but I have failed and I'm not good at VBA. So your help would be greatly appreciated.

I am trying to import a range from a closed workbook into an open workbook.

This is what I have so far:

Sub vbaimport()
' Defines variables
Dim wb1 As Workbook, wb2 As Workbook

' Disable screen updating to reduce screen flicker
Application.ScreenUpdating = False

' Define which workbook is which
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("C:\Users\dan\Documents\Book1.xlsx")

' Copy range A1:A5 from Sheet1 of wb2
wb2.Sheets("Sheet1").Range("A1:A5").Copy
' Paste the copied data to range A1 of the VBAImport sheet in wb1
wb1.Sheets("VBAImport").Range("A1").Paste

' Close wb2
wb2.Close

' Re-enable screen updating
Application.ScreenUpdating = True

End Sub

I expected this to work but I am getting
Run-time error '438':
Object doesn't support this property or method

It is getting as far as opening the workbook and copying the relevant cells but then errors, so doesn't paste those cells into my current workbook.

Any ideas why it's failing?

Following that solution and to take this a stage further, rather than having a set file as wb2, I need to be able to browse to a different file each time. Preferably I would specify a folder in the code and then simply double click on the relevant file. The copied cells will always be the same range.

Can anyone help? Thanks in advance

 

[JumboCactuar]

For your problem try:

wb1.Sheets("VBAImport").Range("A1:A5").Value2 = wb2.Sheets("Sheet1").Range("A1:A5").Value2

Instead of copy/paste

 

[Dan5977]

That works perfectly - thanks so much!

Now if anyone could help me with part 2 of my issue that would be great

Following that solution and to take this a stage further, rather than having a set file as wb2, I need to be able to browse to a different file each time. Preferably I would specify a folder in the code and then simply double click on the relevant file. The copied cells will always be the same range.

 

[JumboCactuar]

Remove your set WB2 line and try this:

 Chdir "C:\"
  FName = Application.GetOpenFilename
  If FName <> False Then
    Set WB2 = Workbooks.Open(FName)
  End If

Untested but it looks right

 

[Dan5977]

That works. Absolutely fantastic. Thanks for your prompt and easy to understand assistance.

One other thing that would be nice to have but is by no means critical. Is there a way to stop it asking me if I want to save the changes when it closes wb2?

 

[Dan5977]

That works. Absolutely fantastic. Thanks for your prompt and easy to understand assistance.

One other thing that would be nice to have but is by no means critical. Is there a way to stop it asking me if I want to save the changes when it closes wb2?

Never mind - This was easy and as such I shouldn't have asked.
I simply added
SaveChanges:=False
after wb2.close

 

[kansfan2001]

Hi,
This worked perfectly loved it!

I hope someone will answer this... I am looking to have wb1 dump the copied information at the bottom of a list but am having some issues getting that done.

I have tried to include an offset but it did not work. All I am looking to do is have this select column b and add what's copied to the next cell that is open. My header starts on B5 which is my constant and everything would be pasted below that point.

wb1.Sheets("VBAImport").Range("A1:A5").Value2 = wb2.Sheets("Sheet1").Range("A1:A5").Value2

Thanks a bunch,
Tim G

 

[Peter_SSs]

@kansfan2001
Welcome to the MrExcel board!

Please take a few minutes to review the Forum Rules
#12 asks you not to post duplicate questions. Therefore I am closing this thread and you should continue only in the other one. In that thread you can provide a link back to this one if required.