Add Data to a Blank Excel File

 I am trying to add a 3 column Table to a closed Excel File which has no Header rows

Source Data is on Sheet1 from Cells A1:C20 - Named as Data

Eg

CLIENT REGION AMT
ABB NORTH 123
CGL SOUTH 145
etc

The Closed File is Called DB.xls and has a sheet called DB_CUST, located in
D:\DATA\DB.xls

There is no data in the closed file DB.xls -* Not even header rows*

The code below adds the data to DB.xls if there are header rows but does not
work if the header rows are not there.

What changes must I make.... other than HDR = NO.  Just by Changing the
HRD=No gives me an error ?

Do I have to change the cursor type ? or include a addnew / movenext
statement in the recordset.....Please help

The below code works if I have headers row in place in the Destination file, howerver it errors out if the destination table is blank

Sub Add_to_Blank_File()
  Dim cnt As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim wbSource As Workbook
  Dim wsSheet As Worksheet
  Dim rnSource As Range
  Dim stConn As String, stSQL As String, stWbtarget As String
  Dim vaSource As Variant
  Dim i As Long, j As Long, EndColoum As Long


  Set wbSource = ThisWorkbook
  Set wsSheet = Sheet1
stWbtarget = Sheet2.Range("VBA_FP") & "\" & Sheet2.Range("VBA_FN")
' or hardcoded sbWbtarget = D:\DATA\Db.xls

  With wsSheet
    Set rnSource = .Range("Data")
  End With

  Set cnt = New ADODB.Connection
  Set rst = New ADODB.Recordset

  stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & stWbtarget & ";" & _
                 "Extended Properties='Excel 8.0;HDR=Yes';"
' I tried chaning Yes to No ….doesnt work

  vaSource = rnSource.Value
  cnt.Open stConn

  stSQL = "SELECT * FROM [DB_CUST$A1:IV65536]"
  With rst
    .CursorLocation = adUseClient
    .Open stSQL, cnt, adOpenForwardOnly, adLockOptimistic, adCmdText
  End With
'  Do I need to change the cursor type..????

  For i = LBound(vaSource) To UBound(vaSource) - 1
    With rst
      .AddNew
EndColoum = Cells.Find("*", After:=Range("IV65536"),
searchorder:=xlByColumns, SearchDirection:=xlPrevious).Column

For j = 1 To EndColoum
       rst(j - 1).Value = vaSource(i + 1, j)
Next j
      .Update
    End With

  Next i

  rst.Close
  cnt.Close
  Set rst = Nothing
  Set cnt = Nothing


End Sub

Connection strings are tricky and it's also tough to experiment with your example (as it's linked to your data), but Yes/No seem strange anyhow.

Have you tried False? Or...removing the parameter all together?

Just a long shot

Another long shot. If your sheet has no headers, ADO might think it has no table. Never done it, but could you use ADOX (or whatever it is called) to create a table first?

Nick, Jan...thanks for your suggestions...I tried both but could not get them to work...could some one please help...

Alternately Is there a way to excure DROP TABLE on a sheet without deleting the header rows...

 TIA

Sam

Excel 03

 Alternately Is there a way to excure DROP TABLE on a sheet without deleting the header rows...

 

You can use something like :

Range(Cells(2, 1), Cells(65536, 4)).ClearContents
OR ALTERNATIVELY
Range("A2:D65536").ClearContents

Are trying to find a named range "Data", with no data and no header row?.

 With wsSheet
    Set rnSource = .Range("Data")
  End With

I'm not sure ADO will recognize a named range that is empty.
In my experience, working with named ranges and ADO is a bit quirky. 
I would suggest, trying to make your code work with hard-coded ranges first, then introduce named ranges.

Here's some code i've used in the past to create tables using ADOX

Private Sub createTable(ByRef adoxParentCatalog As Object, _
                                   ByVal strTableName As String)
   
On Error GoTo Error_Handler

    Dim adoxTable As Object ' ADOX.Table
    Dim adoxColumn As Object 'ADOX.Column
        'Create new instance of ADOX table
        Set adoxTable = CreateObject("ADOX.Table")
        Set adoxColumn = CreateObject("ADOX.Column")
       
            With adoxTable
                .Name = strTableName
                .Columns.Append "CompanyNumber"
                .Columns.Append "Date", 7 ' = adDate
                .Columns.Append "InvoiceNumber"
                .Columns.Append "Value", 6 ' = adCurrency
                .Columns.Append "Status"
                .Columns.Append "Approval"
                .Columns.Append "dteTrans", 7 '= adDate
                ' Provide access to provider specific properties.
                .ParentCatalog = adoxParentCatalog
                ' Allow all fields to be Null.
                For Each adoxColumn In .Columns
                    .Columns(adoxColumn.Name).Properties("Nullable").Value = True
                Next adoxColumn
               
            End With
                ' Add the table to the database otherwise it will be dropped. (Held in memory)
                adoxParentCatalog.Tables.Append adoxTable
               
Exit_Here:
    'Garbage collection
    Set adoxTable = Nothing
        Exit Sub
   
Error_Handler:
   
    Err.Clear
    Err.Raise vbObjectError
          
           Resume Exit_Here
   
End Sub 

HTH

Craig

Craig,

Thanks for your post. I am not very familiar with ADOX so you will have to help me with the following

a) Where do I specify the Path , File name and sheet name of the destination file

b) Where do I specify the Range containing the table that I want to add.

TIA

Sam

Ahh that would help wouldn't it...

Private Sub createAccessDatabase(ByVal strConnectionString As String)

On Error GoTo Error_Handler

    Dim adoxCatalog As Object
       
        'create new instance of ADOX catalog
        Set adoxCatalog = CreateObject("ADOX.Catalog")
                   
            adoxCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                               "Data Source=" & strConnectionString & ";"
                              
        createTable adoxCatalog, "tblName"
        
Exit_Here:
    'Garbage collection
    Set adoxCatalog = Nothing
        Exit Sub
   
Error_Handler:
   
    Err.Clear
    Err.Raise vbObjectError
          
           Resume Exit_Here
      
End Sub 

The connection string is the full database path IE C:\FolderTest\db1.mdb

Please note this code only creates an instance of an access database to look at getting data into and out of Access see this link http://www.excelkb.com/?cNode=1I7C3V&pNodes=8M3M1M.... no point in re-inventing the wheel...

HTH

Craig

Hi Craig,

Thanks for your code... I just modified the connection string for an Excel File...As I need to add data to a Blank Excel File....

I get a compile error on the below module...I have references to Microsoft ActiveX Data Objects 2.8....is there any other reference that I need to tick.... 

        createTable adoxCatalog, "tblName"

Also

a)I dont need to create a table.... The file contains a sheet called say tblName but there is no data in the sheet not even header rows....

b) where in the code do I specify the table that I want to Export....

TIA

Sam