PDA

View Full Version : Copy from one workbook to another



kaostheory
10-30-2008, 08:16 PM
Hey Guys.

Im only a greenhorn at VBA so be kind. Also thanks in advance for any help provided.

Ok. This is probally going to sound stupid or really easy but how would I go about having on open a excel template and upon opening prompt the user to choose the location for the spreadsheet where i will be copying data out of there into the template then saving the template. I figured out the copy functions but open the sheet?

For VBA code im going to call the spreadsheet name for the Template as "Template" and and the spreadsheet to open is going to be called FillDATA and these will be located on the desktop for Template and in C:\FillDATA.xls for FillDATA Spreadsheet

Thanks All
Kaos

GTO
11-01-2008, 06:29 AM
Hi Kaos,

I meant to respond before, and got pulled away. As you have already figured out copying, and are just jammed trying to open or find/open the second file, your question is far from 'stupid'. Not to rant, but the folks I know who lead the most rewarding lives, are the ones who continue to learn throughout life's journey.

Okay, enough on philosophy, just thought to mention - as to the question:

Yeeks! Please allow me to relay some general terms, that I promise you will get better responses to your questions.

Though Excel/Quattro Pro/Lotus etc, are indeed "spreadsheet" programs, if you use the terms "Workbook" or "file" for a file, and "Sheet" or "Worksheet" for a single sheet within a workbook it will be clearer to the person helping.

Also, though you state, "on open a (sic) excel template": after reading your post a couple of times, I feel sure you are referring to a regular workbook. See - an actual template, be it Excel or Word, is designed to create a new workbook or document, based upon the styles/formatting/formulas/etc of the template. Thus, to 'Open' a template, is generally done to redesign it.

Okay, don't know if that was a good explanation, but hope so...

In reading your question, I think (and am checking) that you want the workbook that your are writing the code to, to upon opening, ask the user which workbook to open and copy data from. Is this correct?

Also - since you mention where the target workbook resides, along with the target workbook name, why do we need to check?

Hope that all made sense,

Mark

kaostheory
11-01-2008, 05:03 PM
Hi Mark

Thanks for you reply. The infomation provided will be taken aboard for next time and sorry for the confusion.

"In reading your question, I think (and am checking) that you want the workbook that your are writing the code to, to upon opening, ask the user which workbook to open and copy data from. Is this correct?"

That is 100% correct. At times I rpobaly dont explain things to well. They somehow work out in my head but to others they may seem jiberish.

All thats needed is to prompt a user upon opening to to say "Hey what is the location for the workbook the data is to be copied from" and open that workbook as well so i can copy data out of there.

I hope that answers the question if not please ask as i really appreciate any help.

Thanks Kaos

GTO
11-01-2008, 07:32 PM
...They somehow work out in my head but to others they may seem jiberish.

Hey Kaos,
Well I certainly have had the same thing happen, many a time. Shoot, I still occassionally speak out loud what I've written, just to see if it "sounds right".

Anyways, there are a number of ways to prompt the user to select/open a workbook, so let's try the easiest first and see if that works nicely.

The following code needs to be placed in the module: ThisWorkbook

Hope this helps,

Mark

Option Explicit
Private Sub Workbook_Open()

'// Optional: change the directory (folder) to where the workbook you want to //
'// open is likely to be. In this case, I just used ChDir (Change Directory) //
'// to set the file open dialog to display the same folder as this workbook is //
'// in. //
ChDir ThisWorkbook.Path

'// Display the application's file open dialog box //
Application.Dialogs(xlDialogOpen).Show

'...remainder of your code for copy/paste...

End Sub

kaostheory
11-01-2008, 09:18 PM
Hi Mark

I owe you a drink sometime. Exactly what I was looking for and to think how easy it was.

Cheers
Kaos