with formulas, and then concatenate them into one string. In that case, find a way to write the SQL semi-automatically, e.g. Note: creating this SQL is obviously the messy part if you got 10+years. SELECT Country, "1992" as y1, AS y_valueĪgain, paste this into one cell, adjust the Excel-ranges to your Table1-Range, and read it into a string variable with cmd = YourSheet.Cells(1,2). We use CommandType = xlCmdSql here, which means that our CommandText will be an SQL-String, closely resembling this one: SELECT Country, "1990" as y1, AS y_value The CommandText stores information on what data to get. Editing it in a cell is a lot more comfortable than inside VBA. To keep things simple, paste this into one cell and read it into a string-variable with con = YourSheet.Cells(1,1). Here's how your Connection will likely look, assuming you use an xlsm-File: OLEDB Provider=.12.0 Password= User ID=Admin Įxtended Properties="Excel 12.0 Macro HDR=YES" Check out for more info on other fiole-types. In our case, this is the exact Excel-File we're working on. The Connection / ConnectionString stores information on your linked data (filetype, location, etc). changes to it's content (filters, additional columns, order, calculated fields) can be made with basic SQL-knowledge, eliminating the need to overthink some VBA-logic.įor our purpose, it is largely described with two properties (there's more to it, but we keep it simple):.needs to be set-up once, can be refreshed afterwards.or, as described below, using a QueryTable with some basic SQLĪ query table is basically a link to some external data (a textfile, a database, another excel-doc).by looping your Table1- Range in the x and y direction (like you do).by creating a Recordset, then looping it's records and field-names as shown in this example.
I recon there's a ton of ways to achieve this. K, might aswell give you some hints on how I would do this based on the information I have. Assuming you use the Setup as shown in your example, this worked for me: Sub tt()
0 kommentar(er)
