Results 1 to 5 of 5
2015-04-29, 15:11 #1
- Join Date
- Apr 2015
- Thanked 0 Times in 0 Posts
VBA code to browse to/open an excel file, select data, then transpose it into the original file
I am in need a multi-step macro that will enable me to import specific data from one file into another, using Excel 2013.
The first file is a source file, containing data organized horizontally in records, 10 columns wide. The first cell in the row is the record (row) number (starting at 1). Multiple Users will have their own specific source file, though data is always organized the same in each. This file can contain any number of records, but not likely more than 50, contained in just the one primary worksheet. I'll call it the Source file.
For every record contained in a source file, the User also have a second file, used to track specific activity unique to each record item throughout the entire year. I'll call it the Tracking file.
Each year, Users receive a new Tracking file template because of some details in the template change every year. Up to now, Users have had to manually re-enter the Source file data into the new Tracking data templates. I'd like to incorporate a macro in the Tracking file template, so when activated, the macro will:
(1) Ask if the "Source" file is already open. If so, the macro will proceed to step (2), if not, it will prompt the User to browse to, then open the Source data file, then
(2) Ask the User to select the desired record in the source file by entering the valid index (row) number to identify the correct record for import (this step would have to also validate the index number entered, so the User is required to re-enter the index number of choice if invalid enter is made, i.e. entering "10" when there are only 5 records in the file, or entering a letter or other non-numeric character(s)),
(3) confirm the record selected is correct, then
(4) Select the full horizontal range of data on that record (excluding the index number) and transpose the data into the appropriate vertical range in the Tracking file.
The user could then save and close the Tracking file, before opening another Tracking file and initiating the macro again.
I know how to link the macro to a control button, but since Users will have to give unique names to their respective Source files and all of their Tracking files out of necessity, I cannot simply record these steps in a macro.
I just don't know how to encode all these elements in VBA.
Last edited by VBAgrasshopper; 2015-04-30 at 07:38. Reason: Needed to provide additional detail to ensure clarity.
2015-04-30, 16:14 #2
- Join Date
- Mar 2002
- Newcazzle, UK
- Thanked 466 Times in 444 Posts
Welcome to the Lounge!
What you are asking for is an [update.xlsm] file, which would be distributed to each User.
This file would let the User select their particular [Master] file, from a file selection dialogbox.
Lets assume that each User keeps all of their Tracking files in a particular folder.
The [update.xlsm] file would allow the User to pick their Tracking files folder.
(with a folder selection dialogbox).
The [update.xlsm] file would have a button to 'update' all the tracking files in the specified folder.
A progress bar would show the files being 'updated'.
To give you a sample [update.xlsm] file, we would need to know the file-naming convention used to associate the tracking file with the data record (in order to link the tracker file with its corresponding Master data record).
And an example of a Tracking file would help.
And which 'column' to 'transpose' the data from the record row in the [Master] file.
Last edited by zeddy; 2015-05-01 at 03:05.
2015-04-30, 19:48 #3
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 1,455 Times in 1,324 Posts
A couple more questions.
Is there any reason that once the Source file is identified that the macro can't just loop through it's records creating & naming the necessary Tracking files?
Is there any need for the tracking files to contain the macro after they are created or will other macros be present? I as this so we know whether to save the file as a .xlsx or .xlsm file.
A sample Source & Tracking file would be useful.
2015-05-01, 14:15 #4
- Join Date
- Apr 2015
- Thanked 0 Times in 0 Posts
The 4 main elements:
1) Object(s) - there are hundreds of them, all unique, no two are the same. The activity throughout the year of each object must be tracked. This "tracking" is done by the...
2) The Tracking File User (TFU) - responsible to track any number of objects that have been assigned to them. There are dozens of TFU's, some may track just a couple objects, others may track dozens (remember...every object is unique). To accomplish this task, the TFU uses a...
3) The Tracking File (TF) - an .xlsm file, dense with macros and very complex formulas. It is used constantly thoughout the year by the TFU, has over a dozen worksheets, all of which are password protected to permit prescribed data entry and printing only. Unauthorized and/or unschooled tampering could easily torpedo the intricate calculations and functions within the workbook, so TFU's are never given password access for any reason (notwithstanding the universal vulnerability to those schooled in the black art of password-cracking). Because various constants or variables used in the TF change on an annual basis, a new Master TF Template (MTFT) must be distributed to all TFU's at the beginning of each year.
As a result, the TFU must use the MTFT to create one uniquely named TF for every Object they are assigned to track. Then, the required ID information for the Object must be entered on Sheet1 of the new TF, in a 13row x 2col table. Sheet1 also contains the table of constants & variables governed and maintained by HQ. This, and the other information on Sheet1 of the TF, populate cells or drive the formulas and functions on the other worksheets in the workbook.
4) The Source file (SF) - is a regular .xlsx workbook, having no formulas or macros. Essentially, the SF is the list of Objects that a TFU is required to track. The SF, and the list of objects it contains, is therefore maintained by, and unique to, each TFU. It contains all the Object ID information that must be entered on Sheet1 of an Objects corresponding TF. Other than a title at the top, and a row of column headers, the SF simply stores alpha, numeric, and alphanumeric data in a 40row x 13col table, for up to 40 different objects, in a single worksheet. The first cell in each row is the unique Object Identifier, and the remaining cells in the row contain the other descriptive parameters specific to that Object.
Unlike the TF, the TFU can add/insert rows to the SF, and can modify data for an Object as required. As long as the column structure remains intact, the 40row table in the SF is an arbitrary limit considered to be reasonably adequate for all TFU's. There is no minimum or maximum, so TFU's can add rows if necessary. That said, changes to an Objects data, or the need to add or delete an object is pretty low volume. It's usually the same Objects, year after year, with little or no change to any of their other descriptive parameters.
PHEW! Are you with me so far ...or sorry you asked ?
Up to now, there has been no SF. When a new MTFT was issued at the start of each year, TFU's had to use it to create a new blank TF for each object they tracked, then manually enter the required Object identity data into Sheet1 of each new TF. For those TFU's who track dozens of objects, it's a terribly tedious task, that may have to be repeated, if a revised MTFT has to be distributed. Basically, changes are made to the MTFT every year, while the Objects rarely change, meaning TFU's must enter the same Object data into a new TF every year. This is a fixed process that cannot changed, but it can be improved.
My objective is to end that tediously repetitive task at the start of each year. With the introduction of the SF to store the otherwise static object data, and a macro imbedded in the MTFT, I want to enable the transfer of Object data from the SF to the TF with just a few mouse clicks.
I just need help with the VBA code for the macro, which I've linked to a button on Steet1 of the MTFT. The essential steps of the Macro are:
1) Make the 1st cell in the Object ID table (which is the 1st cell of the target range to receive the transposed object data from the SF) on Sheet1 of the TF the active cell in the TF, then
2) Open a MsgBox to ask if the SF is already open. If yes, go to step (3), if no, open a file dialogue box so the TFU can browse to/open their SF, then go to step (3);
3) Open a userform containing:
a) a ComboBox to list all open workbooks, from which they can select their SF,
b) a RefEdit control to allow them to select a horizontal range in the SF for the Object they want,
c) a Label to display the workbook and range they have selected,
d) a Label or TextBox to ask if the selection is correct, and
e) YES & NO buttons to confirm the range selection (click YES to copy.transpose the selected data into Sheet1 of TF; click NO to re-use RefEdit control and change data range selection).
The TFU could either invoke the macro from the open MTFT to import the data, then save it as an Object TF, or save a version of the MTFT as an Object TF, then import the object data from the SF.
I haven't been able to find any example of code to match this purpose. Some sources indicated that importing (or copying) data from one workbook to another couldn't be done, so I found an example where the userform with combobox & refedit controls would allow the user to choose from among a list of open workbooks (in the combobox), then select the range to be copied.
I don't yet know how to upload samples of the Tracking & Source files, but below is the code I've cobbled together so far from different sources. I know it pretty awful...so I'm desparate for to fix what's broken...and fast. Elements (c) & (d) above have not yet been added to the userform. Several elements of embedded code are Sub's that I'm ATTEMPTING to string together into one. I've therefore just converted several "Sub" and "End Sub" statements into comments.
Thanks for your help...
Here's the code:
'Prompts User to open the Source workbook and
'select object data to import into the
'Object Data Table of the Tracking workbook
'First, select the 1st cell in the range in the Tracking file to RECEIVE the object data from the Source file
'Now make sure the Source file is open
If MsgBox("Is your Source workbook open?", vbYesNo + vbQuestion + vbDefaultButton2, "Source workbook") _
<> vbYes Then
Dim strFileToOpen As String
' *I presently get a Run-time error '1004': Method 'GetOpenFilename' of object'_Application' failed
' *caused by the next 3 lines of code, which I am unable to debug.
strFileToOpen = Application.GetOpenFilename _
(Title:="Please browse to your Source workbook to open", _ '*
FileFilter:="Excel Files *.xls*),")
If strFileToOpen = False Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
' Private Sub ShowObject_Selection()
' Opens userform to allow user to select data range in Source workbook
' Get the name of all the workbooks in the combobox
' *This code will open the userform, but NOTHING is listed in the combobox,
' *just a blank, so I don't know if the RefEdit control will work to select the data
Dim wb As Workbook
For Each wb In Application.Workbooks
ComboBox1 = ActiveWorkbook.Name
'Toggle between all open workbooks
'Private Sub Combobox1_Change()
If ComboBox1 <> "" Then Application.Workbooks(ComboBox1.Text).Activate
Label1.Caption = "": RefEdit1 = ""
'Choose the relevant range
'Private Sub RefEdit1_Change()
Label1.Caption = ""
If RefEdit1.Value <> "" Then _
Label3.Caption = "You have selected [" & ComboBox1 & "] " & RefEdit1 & "." & "If this is correct click OK."
' *Haven't been able to successfully add the other label/textbox that asks if the selection is correct,
' *or the YES/NO buttons
' *This is all the code I have right now.
2015-05-01, 14:31 #5
- Join Date
- Apr 2015
- Thanked 0 Times in 0 Posts
Using an Update.xlsm file to update the many Tracking files is another approach to this. At present, there is not sufficient time to switch to this approach. If however, I'm able to explore it with you further, it could be something to plan for and implement next year.
Currently is no naming convention for the tracking files, so that would have to be developed. I'd then have to incorporate some capacity to test for filename compliance, and notification of any violations, into the updater macro.
Then I'd have to judge which of the two methods was either easier, or had the fewest pitfalls.