Copy data from one workbook to another workbook with same column name

[mhghg]

Hello Guys,
I need a quick script to copy data from my Data Import.xlsx to Team Registration.xlsx When copying they need to ensure the column names are matched.
Thank you
Macy

 

[Nebu]

Hi:

Use the following code.

Sub test()
'Add reference for Microsoft Activex Data Objects Library-Microsoft Activex Data Objects 6.1 Library before running the macro
Application.ScreenUpdating = False
Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'You need to update the below line with your desired path
cpath$ = ThisWorkbook.Path & "\Data Import.xlsx"
rsconn$ = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & cpath & "';" & _
            "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"";"
Conn.Open rsconn

With Sheet1
' You need to update the field names in the below line as per your original file
                i& = .Cells(Rows.Count, 3).End(xlUp).Row + 1
              strSQL$ = "SELECT [First Name],[Last Name],[DOB],[Day Time Phone],[Mobile],[Email],[Street Address],[Suburd],[State ],[Postcode],[Gender],[Medicare Number],[Medicare Person Ref],[Emergency Contact Name],[Emergengy Contact Number] FROM [Sheet1$]"
                On Error Resume Next
                rs.Open strSQL, Conn, adOpenStatic, adLockOptimistic, adCmdText
                ActiveSheet.Range("C" & i).CopyFromRecordset rs
               
End With
Set rs = Nothing
Set Conn = Nothing
Application.ScreenUpdating = True
End Sub

I have made comments on the code, please go through the same before running the macro. This macro will import data from the closed file .Open only your team registration file while running the code.
I am assuming both your files will saved in the same folder.

Thanks

 

[mhghg]

Hi Nebu,
It worked with the Data Import file that I uploaded but it doesn't work with actually file. I don't understand as the file will have the same filename and exactly numbers of columns and names. But if I copy the data from my work file and paste it into the Data Import.xlsx then it works. Any idea?

 

[Nebu]

Hi:

The select statement as per your sample file you had uploaded in the OP, if there is any difference in the layout. upload a sample file here with the difference.

Thanks

 

[mhghg]

Hi Nebu,
What does the OP mean?. There is a new data file. I could not find out what is the difference
Thanks
M

 

[Nebu]

Hi:
OP means Original Poster. In the attached thumbnail select shapes to insert the shape of your choice and right click on the shape and select the option assign macro, it basically serves the purpose of a button. And as far as I am concerned the macro will run just fine if the file you had uploaded is same as your actual file and you follow the instructions I have given in green.

Thanks

 

[mhghg]

Hi
I think you are correct. The script is running but not sure what is wrong with the file?Can you help me to check, i give up today

 

[Nebu]

Hi:

Can you upload the file you are using by removing all the confidential data.
Keep some dummy data in there for me to check.

I guess the reason why it is not picking data is because you might not have given the right file name.

Thanks

 

[mhghg]

Hi Nebu,
Can you help me to find out what is the difference with these files setting
1. Data Import_2.xlsx just normal excel file
2. Table_Import.xlsx is the data imported as the table
As both of them do not work with the team registration file
Big thanks to you.
M

 

[Nebu]

Hi:
Find the attached. I assuming that the name of your data import file will be always the same, if not try to save it in a consistent name.

In the attached file I have give the data import file name as Table_Import you may have to change it as per your need.

Thanks

 

[mhghg]

Hi Nebu,
It works now. I have no idea why. Any way I dont know how to say, but I am really impressed with your enthusiastic to support the learning community
Big clap for u and have a good day!
M

 

[mhghg]

Hi Nebu,
If I got the sql script
for example:
SELECT de.StudId,de.StudLastName, de.StudFirstName, re.CourseTitle, re.AssessmentTitle,
,CAST(re.CreatedDate AS Date) as Date,
,re.YearLevel, re.ActualResult
FROM dbo.StudDet de
RIGHT OUTER JOIN dbo.ewRepStudResults re
ON re.StudId = de.StudId

to get data from sql server how do i modify the connection string from your script. I find it very interesting to connect this way as it does not refresh data.

 

[Nebu]

Hi:

You are talking about SSRS. The code will be something like

SELECT
First Name
,Last Name
,DOB
,Day Time Phone
,Mobile
,Email
,Street Address
,Suburb
,State
,Postcode
,Gender
,Medicare Number
,Medicare Person Ref
,Emergency Contact Name
,Emergency Contact Number
FROM
table Name from where you want to pull data

Note:

You can Join different tables base on your need
types of joins available

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
• FULL JOIN: Return all rows when there is a match in ONE of the tables

You can also also give where statements to filter out data as per your need.

I will not be able to help you without knowing the table names saved in your server and the conditions you want to apply.

Here is a link which talks about SSRS
https://arcanecode.com/category/sql-server-reporting-services/page/2/

Thanks

 

[mhghg]

Hi Nebu
It is not SSRS, just the stored procedure embeddded inside the connection string to get data to excel. But Iwork it out yesterday. I am glad that I did it.
For the SSRS I have no knowledge at all but I will have a look at it when I am better with VBA. One thing at the time you know
Have a good weekend
Macy