Solved

Import Data from one workbook to another using VBA

Posted on 2010-09-20
9
1,139 Views
Last Modified: 2012-05-10
Hello Experts...

I have a workbook open which has a tab called "Previous".  I want to open another workbook using VBA, copy the data from Sheet1 in that workbook, and paste it into the "Previous" tab in the current workbook.

I can get the macro to:
1.) Prompt for the workbook to open
2.) Open the Workbook

Using this code (Found by Googling)

Sub Get_Data()

Dim wbk As Workbook
Dim destWbk As Workbook

Set destWbk = ActiveWorkbook

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose the previous file to import", _
FileFilter:="Excel Files *.xls (*.xls),")
''
    If FileToOpen = False Then
        MsgBox "No file specified.", vbExclamation, "Duh!!!"
        Exit Sub
    Else
        Set wbk = Workbooks.Open(FileToOpen)
    End If

' Need to copy the source and paste it

End Sub

Open in new window


What I can't figure out is how to copy the data from the workbook I opened into my current workbook tab named "Previous".  I've tried many examples, but I just keep getting Paste errors (like error 1004).

I need some help to:
1.) Prompt the user for a workbook to open
2.) Select and copy the contents of the first sheet of that workbook
3.) Paste the copied contents into the "Previous" tab of my current workbook.

Thanks!

0
Comment
Question by:Gridcaster
  • 6
  • 2
9 Comments
 
LVL 10

Expert Comment

by:SANTABABY
ID: 33721536
Try the following code(ATTACHED)


Sub Get_Data()

Dim wbk As Workbook

Dim destWbk As Workbook

Dim xlSheet As Excel.Worksheet

Dim fn As String

        

    Set destWbk = ActiveWorkbook

    FileToOpen = Application.GetOpenFilename _

                (Title:="Please choose the previous file to import", _

                FileFilter:="Excel Files *.xls (*.xls),")



    If FileToOpen = False Then

        MsgBox "No file specified.", vbExclamation, "Duh!!!"

        Exit Sub

    Else

        Set wbk = Workbooks.Open(FileToOpen)

    End If



    Application.CutCopyMode = False

    wbk.Sheets(1).Cells.Select

    Selection.Copy

    destWbk.Activate

    Sheets("Previous").Cells(1, 1).Select

    ActiveSheet.Paste

    

    wbk.Close







End Sub

Open in new window

0
 

Author Comment

by:Gridcaster
ID: 33722237
When I try the code, I get the following error message:

Run-time error '1004': Select method of Range class failed

It is getting stuck on:

[code]
Sheets("Previous").Cells(1, 1).Select
[/code]
0
 

Author Comment

by:Gridcaster
ID: 33722240
Grr... stupid code tags didn't work...

It is getting stuck on:

Sheets("Previous").Cells(1, 1).Select

0
 

Author Comment

by:Gridcaster
ID: 33722242
I am using Excel 2003 if that makes a difference...
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Gridcaster
ID: 33722251
Oh... and I can click in the "Previous" tab and go to Edit -> Paste (or hit Ctrl-V) and the data is pasted into the workbook.  So the select and copy part is working... just the paste part is not.
0
 

Author Comment

by:Gridcaster
ID: 33722291
Ok... so I think I just solved my own problem... The code below seems to do the copy.  I don't know if there is an easier way (or more efficient way) to do this without having to copy everything from the opened workbook (including the bank cells)... So I'm going to leave this question open a bit longer to see if anyone has any suggestions.


Sub Get_Data ()

Dim wbk As Workbook

Dim destWbk As Workbook

        

    Set destWbk = ActiveWorkbook

    FileToOpen = Application.GetOpenFilename _

                (Title:="Please choose the previous file to import", _

                FileFilter:="Excel Files *.xls (*.xls),")



    If FileToOpen = False Then

        MsgBox "No file specified.", vbExclamation, "Duh!!!"

        Exit Sub

    Else

        Set wbk = Workbooks.Open(FileToOpen)

    End If

    

wbk.Sheets(1).Range("A1:IV65536").Copy Destination:=destWbk.Sheets("Previous").Range("A1")

wbk.Close



End Sub

Open in new window

0
 
LVL 10

Expert Comment

by:SANTABABY
ID: 33722546
Glad to hear that you made some progress. Please elaborate what your are trying to copy and what you prefer not to be copied.
Your original posting did not have much detail.
Thanks.
0
 
LVL 13

Accepted Solution

by:
MWGainesJR earned 500 total points
ID: 33724809
This is much more efficient:
Sub Get_Data()  

  

Dim wbk As Workbook  

Dim destWbk As Workbook  

dim ws as worksheet

dim destws as worksheet  

Set destWbk = ActiveWorkbook  

set destws = destwbk.worksheets("Previous")  

FileToOpen = Application.GetOpenFilename _  

(Title:="Please choose the previous file to import", _  

FileFilter:="Excel Files *.xls (*.xls),")  

''  

    If FileToOpen = False Then  

        MsgBox "No file specified.", vbExclamation, "Duh!!!"  

        Exit Sub  

    Else  

        Set wbk = Workbooks.Open(FileToOpen)

        set ws = wbk.worksheets("Sheet1")  

    End If  

  

ws.usedrange.copy destws.cells

  

End Sub

Open in new window

0
 

Author Closing Comment

by:Gridcaster
ID: 33725789
Works perfectly!  Thanks for your help!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now