SQL query with same name columns-CodePudding

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:

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.

CodePudding user response：

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

CodePudding user response：

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.