Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Self Populate from Excel FILE to FILE

0

I need to create a macros to import date from one Excel FILE to another Excel FILE.

Or a better way to accomplish this.

I have created a separate file for each client that users can input data. I want to then have that data self populate the file that is compiling all answers.

Answer
Discuss

Discussion

Hi Lois and welcome to the forum.
If you could provide a sample client file and a sample compiling file that would go a long way to help us provide you with a solution.
Given the minimal info you have provided:
1) is the data to be exported a single row of data?
2) is the data always the last data entered?
3) is the data entered via a UserForm or entered directly to the file?
There are two approaches to this: i) the compiler file could have a macro which requires selecting the file for the data to import or, ii) have each client file contain a macro (same macro in each file) which would export the data to the compiling file.
WillieD24 (rep: 557) May 11, '23 at 11:11 am
Sure, here is the "form" people fill out. It contains conditional formatting and data validation.

Team MS/DB/JS     Rank A     Date Updated       # of Devices 16     Date Tech Plan Was Presented Unknown     UPS Install Date       Date of UPS Battery Change       Is There a Current Network Diagram No     Virtualization VMWare 4.x     Oldest server OS Windows 2016     Oldest Workstation OS Windows 10 - 22H2      Back up & Disaster Recovery       Sync to USB/Offsite       ALL Servers Backed Up Yes     Archival Back Up       Workstation Back Up       Annual External Network Scan Performed No     Internet Security Appliance Meraki Model Ends w/ 4 or 7
LoisBats May 11, '23 at 11:16 am


This is a better representation
LoisBats May 11, '23 at 11:18 am
I paste the table in this box and it looks like a table, but when I hit send, it changes it to text. What is the best way to get it to you in table form?



Team MS/DB/JS     Rank A     Date Updated       # of Devices 16     Date Tech Plan Was Presented Unknown     UPS Install Date       Date of UPS Battery Change       Is There a Current Network Diagram No     Virtualization VMWare 4.x     Oldest server OS Windows 2016     Oldest Workstation OS Windows 10 - 22H2      Back up & Disaster Recovery       Sync to USB/Offsite       ALL Servers Backed Up Yes     Archival Back Up       Workstation Back Up       Annual External Network Scan Performed No     Internet Security Appliance Meraki Model Ends w/ 4 or 7
LoisBats May 11, '23 at 11:20 am
Lois

You've tried to explain but Willie needs Excel files really/ Otherwise he will take ages trying to recreate your situation before he tries to fix it (and it would be the same for me).

To add a file (or two), please edit your original question and use the Add Files... buttons to upload representative Excel files (without any personal data) to show your existing data (and any macros). Then we should be able to give specific help. Thanks in advance.

Note too that you can't paste or upload images (check your posts above)
John_Ru (rep: 6232) May 11, '23 at 11:24 am
@Lois - thanks for providing the files. 
@Willie - I assume youll reply on this one. If not, I might get time tomorrow
John_Ru (rep: 6232) May 11, '23 at 12:52 pm
@John,
If you beat me to the punch I won't mind. I will try to look at this later tonight or tomorrow.

Cheers   :-)
WillieD24 (rep: 557) May 11, '23 at 2:45 pm
Ok, thank you for your help. I will not be at work tomorrow, but I will be sure to try your suggestions Monday.
LoisBats May 11, '23 at 2:49 pm
@Lois
I had a quick look at both files and noticed a few things which concern me and need clarification before I put any effort into this. (Yes, they are "sample" files but sample files need to acurately show the structure of the real world files)
1) The source file has 18 "categories" but only 17 are in the summary file - "Team" is not in summary file. Team is not important to the summary?
2) The source file categories are in column "B", the summary file categories are in rows. This is not a deal breaker, however, along with the empty rows in the source file, means the macro will be more complex.
3) There are 35 categories in the summary file. Where does the data come from for the extra 17 categories? Usually a summary file has the same (or fewer) categories than the source.
4) There are external links (data validation) in the source file. Do these links need to be copied to the summary file? I don't understand the need for these links in the summary file. After the data from the source file is copied to the summary file, why would you want to allow someone to change it.
5) The 3 rows of data in the summary file are not chronological.
6) There is not a consistant number of blank rows between summary file the data.
7) The categories (titles) in the source file do not exactly match the categories in the summary file. This can make reading/comparing a bit difficult.
8) In the real world, will either of these files be password protected?
WillieD24 (rep: 557) May 12, '23 at 9:56 am
Add to Discussion

Answers

0

Hi Lois,

I have not tested the following code snippets, but you (or someone with VBA skills) should be able to use these as "building blocks" to achieve what you want.

Both workbooks need to be open to copy data from one to the other. Also, both workbooks should be saved in the " .xlsm " file format. Your finished macro needs to be placed in a module in the Soucre workbook(s).

Step one is to declare the variables:

' declare variables
Dim wb As Workbook
Dim SumFilePath As String
SumFilePath = "C:\Excel\Summary.xlsm"
Dim DataToCopy As Variant
Dim SrcDataRow As Long
Dim SrcDataCol As Long
Dim LastDataRow As Long
Dim NewDataRow As Long
Dim NewDataCol As Long
Dim ReturnVal As String

Step two is to check if the Summary workbook is open:

' check all currently open workbooks
For Each wb In Workbooks
If wb.Name = "Summary.xlsm" Then
    ReturnVal = "fileopen"
End If
Next

If ReturnVal <> "fileopen" Then
Workbooks.Open (SumFilePath)
End If

Step three is to use a loop and copy the data from Source to Summary

    ' find last row with data in source file
LastDataRow = Range("B" & Rows.Count).End(xlUp).Row
SrcDataRow = 3   ' first row of data to copy
SrcDataCol = 2   ' column "B" with source data
    ' find last row with data in Summary file
NewDataRow = Summary.xlsm.Range("A" & Rows.Count).End(xlUp).Row + 1
NewDataCol = 1   ' first column to copy data to


Do Until SrcDataRow > LastDataRow
    DataToCopy = Cells(SrcDataRow, SrcDataCol).Value
    SumFilePath.Cells(NewDataRow, NewDataCol) = DataToCopy

    SrcDataRow = SrcDataRow + 2
    NewDataCol = NewDataCol + 1
Loop

Step four is to save the changes to the Summary workbook and then close it:

' save changes to Summary workbook and close file
SumFilePath.Close True

Hope this helps; if so please mark the answer as selected.

Cheers   :-)

Discuss

Discussion

@Lois please comment (at least) on Willie's Answer. 
John_Ru (rep: 6232) May 18, '23 at 12:21 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login