Solved

Import Data from one workbook to another using VBA

Posted on 2010-09-20
9
1,156 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:Gridcaster
ID: 33722242
I am using Excel 2003 if that makes a difference...
0
 

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

739 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