14 Replies - 528 Views - Last Post: 21 November 2019 - 10:08 AM Rate Topic: -----

#1 Whateva_   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 76
  • Joined: 28-August 16

Best way to copy data from a closed workbook to another using vba...

Posted 20 November 2019 - 09:01 AM

What is the best/most efficient way of copying data from one closed workbook to another and close the input workbook without saving and close the output workbook with saving changes?

Also, if I do copy pastes multiple times between the two what is the best way to do it without opening and closing the same files multiple times.

Below is a sample code that I did:
Dim FSO
Dim sFile, tFile As String

sFile = TextBox1.Text
tFile = TextBox2.Text

Set FSO = CreateObject("Scripting.FileSystemObject")

If Not FSO.FileExists(sFile) Then
    MsgBox "Specified Input File Not Found", vbInformation, "Not Found"
Else
    If Not FSO.FileExists(tFile) Then
        MsgBox "Specified Output File Not Found", vbInformation, "Not Found"
    Else
        
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    Dim src, res As Workbook
    
    Set src = Workbooks.Open(TextBox1.Text)
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    
    Cells.Find(What:="Address", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Set res = Workbooks.Open(TextBox2.Text)
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    res.Worksheets("mysheet").Activate
    Range("H10").Select
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
    
    
    res.Close

    src.Close False

ErrHandler:
        Application.EnableEvents = True
        Application.ScreenUpdating = True

    End If
End If


The code is trying to open the input file first, then search the file for the cell having a value Address, then get all items from that column except the Address cell and then open the output file and paste that in a given location, then save the output file close it and close the input file without saving and do this for other cell values as well and not just Address.

But the code is not doing what I intended it to do...Can anyone help ://>

Is This A Good Question/Topic? 0
  • +

Replies To: Best way to copy data from a closed workbook to another using vba...

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15506
  • View blog
  • Posts: 62,082
  • Joined: 12-June 08

Re: Best way to copy data from a closed workbook to another using vba...

Posted 20 November 2019 - 09:06 AM

What language is this in?


Quote

What is the best/most efficient way of copying data from one closed workbook to another and close the input workbook without saving and close the output workbook with saving changes?

Depends on the language.


Quote

f I do copy pastes multiple times between the two what is the best way to do it without opening and closing the same files multiple times.

Get all your data at once, store in variables and objects, and then apply them to the second. It's about smart code design.
Was This Post Helpful? 0
  • +
  • -

#3 Whateva_   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 76
  • Joined: 28-August 16

Re: Best way to copy data from a closed workbook to another using vba...

Posted 20 November 2019 - 09:12 AM

View Postmodi123_1, on 20 November 2019 - 09:06 AM, said:

What language is this in?


It's VBA for excel.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15506
  • View blog
  • Posts: 62,082
  • Joined: 12-June 08

Re: Best way to copy data from a closed workbook to another using vba...

Posted 20 November 2019 - 09:28 AM

So you are running this in one of the two Excel spreadsheets or from a third?
Was This Post Helpful? 0
  • +
  • -

#5 Whateva_   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 76
  • Joined: 28-August 16

Re: Best way to copy data from a closed workbook to another using vba...

Posted 20 November 2019 - 09:29 AM

Yes, exactly...
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15506
  • View blog
  • Posts: 62,082
  • Joined: 12-June 08

Re: Best way to copy data from a closed workbook to another using vba...

Posted 20 November 2019 - 09:37 AM

There was an 'or' in there.

"yes" doesn't differentiate which of the two things I asked about.
Was This Post Helpful? 0
  • +
  • -

#7 Whateva_   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 76
  • Joined: 28-August 16

Re: Best way to copy data from a closed workbook to another using vba...

Posted 20 November 2019 - 09:47 AM

Oops, my bad, I didn't read that properly. I'm running the code from a third workbbok.
Was This Post Helpful? 0
  • +
  • -

#8 Atilla999   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 20-November 19

Re: Best way to copy data from a closed workbook to another using vba...

Posted 21 November 2019 - 03:07 AM

Maybe it's a good idea if you tell us what you try to accomplish here. That helps to understand how that could be done.
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,345
  • Joined: 12-December 12

Re: Best way to copy data from a closed workbook to another using vba...

Posted 21 November 2019 - 04:13 AM

Quote

Also, if I do copy pastes multiple times between the two what is the best way to do it without opening and closing the same files multiple times.


If your intention is to copy and paste multiple times between two workbooks then it is not clear why you would want to close the file(s) during the process? Is there some delay, or some process, between paste operations that would make closing the workbook(s) useful or necessary?

Generally, it is best to open and automate workbooks for a short a space as possible, and to limit operations between them. For example, if you can get all the data you need in one step, paste it into the other workbook, then close this workbook, it is a good idea. Then, with the data in the other workbook, you could move the pasted data to where it is needed.

An example of a recorded macro that selects multiple regions from a single workbook (using Ctrl/Shift combinations) and pastes them into another.

Sub Macro1()
'
' Macro1 Macro
'

'
    Windows("Book2").Activate
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A13,D2").Select
    Range("D2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A13,D2:D13,E2").Select
    Range("E2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Book3").Activate
    Range("B2").Select
    ActiveSheet.Paste
    Windows("Book1").Activate
End Sub


The whole process would be facilitated with the assistance of Named Ranges.
Was This Post Helpful? 0
  • +
  • -

#10 Whateva_   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 76
  • Joined: 28-August 16

Re: Best way to copy data from a closed workbook to another using vba...

Posted 21 November 2019 - 06:57 AM

For better understanding find below a sample input file:

SL. NO.		NAME            ADDRESS   	EMP. ID    MONTHLY SALARY     YEARLY SALARY			AGE 
1       	S. ADAMS        NY, USA         Q_0014      $450.00            $5,400.00        42  
2       	P. ROY          CO, USA         Q_0101      $500.00            $6,000.00        29  
3       	J. RUDOLF       MO, RUSSIA      Q_0135      $600.00            $7,200.00        35  
4       	K. BRYAN        QU, CANADA      Q_0005      $480.00            $5,760.00        22  
5       	L. LEWIS        QU, CANADA      Q_0002      $450.00            $5,400.00        25  
6       	F. CHAN         ZJ, CHINA       Q_0099      $650.00            $7,800.00        30  
7       	O. McGRATH      WB, INDIA       Q_0122      $400.00            $4,800.00        32  
8       	J. DEAN         NY, USA         Q_0083      $550.00            $6,600.00        49  
9       	I. LORKE        QU, CANADA      Q_0144      $500.00            $6,000.00        40  
10      	P. BRIEH        ZJ, CHINA       Q_0011      $380.00            $4,560.00        33  
        	                                            $4,960.00          $59,520.00       33.7




sample output file (before macro run):

E_ID	NAME	DOJ	MONTHLY SALARY	POLICY NO.
				
				
INCRP. 302156 (EMP_DET_DATA)				



sample output file (after macro run):

E_ID	NAME	  DOJ	MONTHLY SALARY	POLICY NO.
Q_0014	S. ADAMS		 $450.00 	
Q_0101	P. ROY		       $500.00 	
Q_0135	J. RUDOLF		 $600.00 	
Q_0005	K. BRYAN		 $480.00 	
Q_0002	L. LEWIS		 $450.00 	
Q_0099	F. CHAN		       $650.00 	
Q_0122	O. McGRATH		 $400.00 	
Q_0083	J. DEAN		       $550.00 	
Q_0144	I. LORKE		 $500.00 	
Q_0011	P. BRIEH		 $380.00 	
				
				
INCRP. 302156 (EMP_DET_DATA)				



Hope this help understand what I'm trying to do...
Was This Post Helpful? 0
  • +
  • -

#11 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,345
  • Joined: 12-December 12

Re: Best way to copy data from a closed workbook to another using vba...

Posted 21 November 2019 - 07:23 AM

Yes, I kinda guessed it was something like that.

You should preferably have at least one blank row (and column) between the table of data and any totals or other information. You could then use, specifically, the Table feature of Excel, or name the area and/or columns of data. These names are invaluable when coding against them. (It would also be preferable if dots and spaces could be removed from the column headings, preferring underscores.)

My recorded macro remains relevant. You could copy the columns of data you need to a blank area or worksheet of the other workbook; then move the columns to where they are needed.
Was This Post Helpful? 0
  • +
  • -

#12 Whateva_   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 76
  • Joined: 28-August 16

Re: Best way to copy data from a closed workbook to another using vba...

Posted 21 November 2019 - 08:12 AM

Hi andrewsw, can you just update your code with the both file opening and closing process as mine is not working and it is not the most efficient way to go about. I would really appreciate it.
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,345
  • Joined: 12-December 12

Re: Best way to copy data from a closed workbook to another using vba...

Posted 21 November 2019 - 08:17 AM

If your code is failing to open and/or close then post the relevant code together with any error details and other relevant information. I would, if anything, only be posting bog standard examples of Workbooks.Open and Workbook.Close, which is well documented.
Was This Post Helpful? 0
  • +
  • -

#14 Whateva_   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 76
  • Joined: 28-August 16

Re: Best way to copy data from a closed workbook to another using vba...

Posted 21 November 2019 - 08:21 AM

I've already posted the code I've done in the beginning of this post.
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,345
  • Joined: 12-December 12

Re: Best way to copy data from a closed workbook to another using vba...

Posted 21 November 2019 - 10:08 AM

You are using On Error and then ignoring any error details. This won't help you to resolve issues.


At least you should display the error details.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1