importing data from external software into excel

hello,

I need to create a macro that would copy across the data from an external software, see screenshot, into an excel spreadsheet.
The idea is that I am getting tons of files in that software ( which is based on SQL server if it can help) , and i need to copy across the column "name" , date, KP start & end ... and it would be so usefull to have an automatism.

does anyone know where to start?
many thanks!

-->Write query you need in SQL Server.
--> Go to Excel --> Data --> From Other Sources --> From SQL Server
--> Type in the server name (in your case looks to be like butendiek) & enter the login name & password if its set & connect to the server
--> Once connected, Select the table you need, click next & Finish.
--> In the bottom, it will give you something like click here to cancel (as shown in screenshot), click on it & stop the query as this might not be what you want
--> Once thats done, click on the name of the server which will be the cell where you decide to put it initially, this will highlight the "Properties" tab in the data field
--> Click on properties & then properties again
--> Go to definition --> Change command type to SQL & paste your SQL command which you have.
--> Click okay & this will refresh your data & get the data you need
--> Once done, you can go back to the same tab & export the connection file, this can be opened whenever you need it & will refresh automatically since it is connected to SQL server.
Hope this helps

Capture.PNG

Originally Posted by siddharth_s1

-->Write query you need in SQL Server.
--> Go to Excel --> Data --> From Other Sources --> From SQL Server
--> Type in the server name (in your case looks to be like butendiek) & enter the login name & password if its set & connect to the server
--> Once connected, Select the table you need, click next & Finish.
--> In the bottom, it will give you something like click here to cancel (as shown in screenshot), click on it & stop the query as this might not be what you want
--> Once thats done, click on the name of the server which will be the cell where you decide to put it initially, this will highlight the "Properties" tab in the data field
--> Click on properties & then properties again
--> Go to definition --> Change command type to SQL & paste your SQL command which you have.
--> Click okay & this will refresh your data & get the data you need
--> Once done, you can go back to the same tab & export the connection file, this can be opened whenever you need it & will refresh automatically since it is connected to SQL server.
Hope this helps

Attachment 385466

Thank you,

Well the software, uses two ODBC connections. One named ‘LocalSQLServer’ used for all import/export/editing and one named ‘JPAdmin’ for database administration like creating a new database and attaching an old database.

the server name is LocalSQLServer, I tried to connect it but I got an error, see screenshot "1"
I'm attaching the settings of my SQL server as well,

so far, the issue is that the server name doesn't get recognized... please let me know, many thanks!

Is the server a local server for your machine only? It does not have a special password.
You are using Windows credentials to logon to the server. Or if it is on your company's server, does it use SSO?

Originally Posted by siddharth_s1

Is the server a local server for your machine only? It does not have a special password.
You are using Windows credentials to logon to the server. Or if it is on your company's server, does it use SSO?

Yes it is a local server, I have configured the SQL server on my own laptop for practicing purposes, it does not have a special password ( or at least im not sure about it, ) how can i make sure about it? can it be eventually the same password of the logon?

It is my own laptop, i just have a password to logon windows and that's it as far as i know, definitely no SSO

The table you have opened in the original screenshot, where is that table situated?
Where on the computer?

Try,
Click Studio, then when you are asked to get connected to server, copy the server name from the server name box as it is and paste it in to excel.
If you are able to connect to SQL using the same server name, I can not see why you can not from excel using the same sever name. I have just tried it and it works

Agreed to AB33, & moreover if the database file i.e. the .mdf file is on your local system, I suggest you to put it into C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

This will ensure that the table will be available in the dropdown when you connect to "localhost" server name. There is no need of an ODBC my friend.