SQL query with same name columns

user_19361922 0
Viewed 48

I am using excel for a macro to paste information with a sql query but in the table where I have the information in the columns I have the same repeated name and the names must be those.

The macro would be the following:

"Select [code], [name], [PCR] from [Book$B2:H]"

The table where I want to get the information would be the following:

enter image description here

I need the query to copy the information that is in bold but i have PCR in 3 columns so its getting only the first one.

vba excel sql
2 Answers

If you're using ADO in your VBA code, then you can change the connection string to say that your data doesn't have headers. This then allows you to refer to fields by their position rather than their name. To do this, add HDR=No into the Extended Properties of the connection string.

Your SQL query could then be something like this:

SELECT F1, F2, F4, F6, F8 FROM [Book$C2:H]

Setting up the connection string would be something like this:

' Set up connection
Dim cn As Object
Set cn = CreateObject("ADODB.Connection")

' Connection string for Excel 2007 onwards .xlsm files
With cn
   .Provider = "Microsoft.ACE.OLEDB.12.0"
   .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=""Excel 12.0 Macro;HDR=No"";"
    .Open
End With

This assumes that your VBA code is in the same workbook as the data - if that's not the case, then just change the value for the Data Source. See connectionstrings.com for any other potential variations you might need to make for different types of Excel file

barrowc_21275080

The easiest solution is likely to involve:
Insert a row C.
in C3: =if(C1="",B1&"."&C2,C1&"."&C2) and drag across.

But to fit into the bigger picture we would need to know about the bigger picture.

user_152134840
Related