VBA Copy to Unopened Workbook

Luke777

New Member
Joined
Aug 10, 2020
Messages
19
Office Version
  1. 2007
Platform
  1. Windows
Hi all,

I've recently written some code, the crux of which (there's a lot omitted here) looks like this...

VBA Code:
Sub CopySend()

'Main: Copy and paste to database code'
Range("B2:M2").Copy Workbooks("Incident Log - Database.xlsm").Worksheets("All Incidents").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub

Basically, a button is pressed on a sheet>range B2:M2 is then copied over to the next empty row in "Incident Log - Database.xlsm". That part works a wonderfully, but being very new to this, I was a bit disappointed when I realized the destination workbook needs to be open for this bit of code to function correctly.

Is there a way to make it work without the destination workbook being open? I cannot allow users to open the Database file for GDPR reasons.

Thanks everyone :)
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Sorry for the repeated replies - I got the code to do something. It's too fast to see what's going on (as you mentioned) which is good, but unfortunately no data is being transferred between the two workbooks at present. I'll do some more tinkering and report back.
Just in case you missed it - the code you are exploring opens the workbook you said you didn't want opened, so its visible. Should the user hit esc or ctrl+break during execution, or some other unexpected event causes a break, the workbook will be exposed.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Luke777

New Member
Joined
Aug 10, 2020
Messages
19
Office Version
  1. 2007
Platform
  1. Windows
It will be closed before they know what happened
Code:
Sub PutDataIntoClosedWorkBook()
Dim fPath As String, fName As String
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
Set wb1 = Workbooks("Desktop Book4.xlsm")  'Change name to suit
Set sh1 = Workbooks("Desktop Book4.xlsm").Worksheets("Sheet1")  'Change name to suit
fPath = "C:\Users\Luke\Desktop" & Application.PathSeparator 'Change path to suit
fName = "Desktop Book3.xlsm"  'Change name to suit
Workbooks.Open (fPath & fName)
Windows("Desktop Book4.xlsm").Activate  'Change name to suit
Set wb2 = Workbooks("Desktop Book3.xlsm")  'Change name to suit
Set sh2 = Workbooks("Desktop Book3.xlsm").Worksheets("Sheet1")  'Change name to suit
    sh2.Cells(10, 4).Resize(13).Value = sh1.Cells(8, 11).Resize(13).Value
wb2.Close SaveChanges:=True
End Sub
Okay, so I've managed to get this code working! Had to change a few of the Cells() values which caught me out, but it works PERFECTLY so thanks for that! I just need to add a bit of code that stops it from overwriting itself every time I add new data to the destination workbook - but I *think* I should be able to work that one out... possibly lol.

Thanks again to both of you that have helped here :)
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,658
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
"C:\Users\Luke\Desktop" is the path to your desktop. Verify and change if required.
"Desktop Book3.xlsm" is a workbook saved to your desktop. If a different name, change wherever needed.
"Desktop Book4.xlsm" is the workbook with the code in it and is housed in another folder somewhere.
"sh1" and "sh2" are worksheets in "Desktop Book4.xlsm" and "Desktop Book3.xlsm" respectively.
"sh1.Cells(8, 11).Resize(13)" is Range("K8:K22") in sh1. It is the data that will be transferred (copy if you will) to sh2.
"Cells(10, 4)" is the top cell in sh2 where the data from sh1 will start. It needs to be resized to the same size as the data from sh1.
The naming is confusing. I should have made that a little more self explanatory.
 

Luke777

New Member
Joined
Aug 10, 2020
Messages
19
Office Version
  1. 2007
Platform
  1. Windows
Just in case you missed it - the code you are exploring opens the workbook you said you didn't want opened, so its visible. Should the user hit esc or ctrl+break during execution, or some other unexpected event causes a break, the workbook will be exposed.
Yeah, thank you for pointing that one out, I realized, but it's still helpful for anyone else that happens along this thread. I may end up having to scrap this method entirely - but as far as I can tell, all variations of what I'm trying to do have a flaw one way or another. Thanks again though!
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
233
Office Version
  1. 365
Platform
  1. Windows
Hello Luke
While the workbook would still be opened, it might help if you placed the following line of code at the beginning (before anything happens) of the macro. This will freeze the screen to whatever is already there.
Application.ScreenUpdating = False
Now at the end after everything is completed, place the following line of code. This will unfreeze the screen.
Application.ScreenUpdating = True
To reduce the time available for anyone to break the code operation, as much as possible, I'd place any code OUTSIDE of the 'Open' and the 'Close' commands. In other words, get that file closed again as quickly as possible. I hope this will be of some help.

TotallyConfused
 

Watch MrExcel Video

Forum statistics

Threads
1,114,486
Messages
5,548,347
Members
410,828
Latest member
A9Bosv3
Top