[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Use vba to open excel 2007 file and copy the first row

Posted on 2011-10-12
8
Medium Priority
?
344 Views
Last Modified: 2012-05-12
Hi Experts,

How do I programmatically open an excel file from a saved location and then copy the first row of data and paste the values into column B of another worksheet?  I would like to be able to do this using a form button in excel 2007.  Any help would be greatly appreciated.  Please see the sample files attached. Test.xlsx
Test2.xlsx
0
Comment
Question by:daintysally
  • 4
  • 3
8 Comments
 
LVL 9

Expert Comment

by:experts1
ID: 36957922
Use code below in bottun click procedure of your Form button:


Sub CopyFromBook()

    'Open target workbook here
    Workbooks.Open Filename:= "C:\MyWorkbook.xls"

    'Select the Row to copy from here (First Row only)
    Rows("1:1").Select

    'Copy contents of the selected Row here
    Selection.Copy

    'Activate the Original WorkBook here
    Windows("OriginalWorkBook").Activate

    'Select the Row to paste into from here (First Row of original book)
    Rows("1:1").Select

    'Paste into selected cell here
    ActiveSheet.Paste

    'Close target workbook here
    Windows("MyWorkbook").Close
End Sub
0
 

Author Comment

by:daintysally
ID: 36958415
I want to paste the values into column B of the excel spreadsheet instead of pasting it to another row.  Can I transpose the row values into single column values per cell?
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 36958523
Yes you can :)

Is this what you are trying? Please amend it as per your requirements.

Option Explicit

Sub Sample()
    On Error GoTo Whoa
    
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    '~~> Destination
    Set wb1 = ActiveWorkbook
    Set ws1 = wb1.Sheets("Sheet2")
    
    '~~> Source
    Set wb2 = Workbooks.Open(Filename:="C:\test2.xls")
    Set ws2 = wb2.Sheets("Sheet1")
    
    ws2.Rows("1:1").Copy
    ws1.Range("B1").PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    
    wb2.Close savechanges:=False
    
    Set ws2 = Nothing
    Set wb2 = Nothing
    
    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

Open in new window


Sid
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:experts1
ID: 36958772
If you need to copy and paste to a single cell or range of cells example "A1", then

replace "Rows("1:1").Select" with Range(("A1").Select"

The same as above to select cell or range to paste into!.
0
 

Author Closing Comment

by:daintysally
ID: 36962667
Thank you!!! This worked perfectly!! :):)
0
 
LVL 9

Expert Comment

by:experts1
ID: 36963566
SiddharthRou's solution was very much
similar to mine, but I guess you did not test
my solution which was submitted first?
0
 

Author Comment

by:daintysally
ID: 36963786
Yes, you are correct, your solution was very similar...however, I responded asking to paste the values in a column instead of another row and SiddharthRou responded first with a complete solution.  I meant no harm...please forgive me if I have offended you in any way.  Is there anything that I can do to give you some of the points?
0
 
LVL 9

Expert Comment

by:experts1
ID: 36964994

I just wanted to know if you had tested  my solution!

Also, I guess the points have already been allocated, so
nothing to do here.

Regards
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

834 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