none
XLS ADO-connection: ignore formatting from header column RRS feed

  • Question

  • All

    I have an Access 2010-database which opens an Excel 2010-template file via ADO. The Excel template has an existing sheet with some column names in first row. This first row has some formatting such as a grey background.

    Data is being added with an INSERT-statement. However, these new rows get the same formatting as row 1 whereas I want to ignore the formatting.  How can this be done?

    Private Function XLS_Connection(strFile As String) As ADODB.Connection
    Set XLS_Connection = New ADODB.Connection
    With XLS_Connection
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & strFile & ";" & "Extended Properties=""Excel 12.0;HDR=Yes"""
        .Open
    End With
    End Function
    
    
    Private Sub Export()
    Dim cnXLS As ADODB.Connection
    Dim strSQL As String
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("qryCountries")
    Set cnXLS = XLS_Connection("template.xlsx")
    
    While Not rs.EOF
        strSQL = "Insert into [Output$](Country,City) values('" & rs!Country & "','" & rs!City & "')"
        cnXLS.Execute strSQL
        rs.MoveNext
    Wend
    cnXLS.Close
    End Sub

    Monday, February 23, 2015 10:02 AM

Answers

  • Dear

    As this issue was rather urgent I had to search for an alternative solution: I removed all formatting from the template file. The data is first written into the XLS over ADO. Next, I open the XLS as an object and add the formatting of the header rows.

    • Marked as answer by kvhoof Wednesday, February 25, 2015 9:52 AM
    Wednesday, February 25, 2015 9:52 AM

All replies

  • Hi
    Thanks for posting in MSDN forum.

    Since the issue is complex, I'm trying to involve some senior engineers into this issue and

    it will take some time. Your patience will be greatly appreciated.
     
     Sorry for any inconvenience and thanks for your understanding
     
    Best Regards


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, February 25, 2015 8:26 AM
    Moderator
  • Dear

    As this issue was rather urgent I had to search for an alternative solution: I removed all formatting from the template file. The data is first written into the XLS over ADO. Next, I open the XLS as an object and add the formatting of the header rows.

    • Marked as answer by kvhoof Wednesday, February 25, 2015 9:52 AM
    Wednesday, February 25, 2015 9:52 AM