Exporting an access table into an exisiting excel spreadsheet versus a new spreadsheet 1

[irethedo]

I am exporting the contents of an Access table into an existing excel spreadsheet but I then
tried this with a non-existing spreadsheet and it did not create the spreadsheet.

What am I missing in order to create a new spreadsheet and export the contents of an Access table into that new excel spreadsheet?

thanks

I am using the following code to export to an existing spreadsheet but it will not create a new spreadsheet:

Dim LineNum As Integer
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim objXL As Object
Dim objWkb As Object
Dim objSht As Object

Set objXL = CreateObject("Excel.Application")
Set db = CurrentDb
Set rs1 = db.OpenRecordset("NW_Excel_tbl", dbOpenSnapshot)
    
With objXL
    .Visible = True
        
Set objWkb = .Workbooks.Open("C:\Stuff\my.xlsx")
Set objSht = objWkb.Worksheets("works")         'RSP
        
objWkb.Worksheets("RSP").Activate
       
 With objSht
      .Range("A" & lngLastRow).CopyFromRecordset rs1
End With

    Set rs1 = Nothing
    Set objSht = Nothing

 

[combo]

Depending on situation, you may either add worksheet to existing workbook or create a new workbook and paste to first worksheet.
First case:

With objXL
    .Visible = True
    Set objWkb = .Workbooks.Open("C:\Stuff\my.xlsx")
    With objWkb.Worksheets.Add         ' returns new worksheet
        .Range("A1").CopyFromRecordset rs1
        .Name = "SheetWithNewData"
    End With
    Set rs1 = Nothing
    ' continue with workbook and excel application

Second case:

With objXL
    .Visible = True
    Set objWkb = .Workbooks.Add
    With objWkb.Worksheets(1)
        .Range("A1").CopyFromRecordset rs1
        .Name = "SheetWithNewData"
    End With
    Set rs1 = Nothing
    ' continue with workbook (has to be saved by SaveAs) and excel application

combo

 

[irethedo]

Thanks for the reply an good info Combo


I updated this database to use an ini file that defines what the exported excel file name is and
where it is to be stored...

In most cases the user will want to export to the same existing spreadsheet but as an option, if the
outfile name in the ini file is set to SAME, then the access table is to be exported to a new excel
spreadsheet that uses the sales order number for the file name.

Regardless if the table is being exported to an existing excel spreadsheet or if it is being exported
to a new excel spreadsheet, I want the worksheet where the data is being exported to the same name worksheet
in both scenarios.

So when the outfile variable that is specifies in the ini file is set to SAME, the following code creates a
spreadsheet with the sales order number as the file name but it currently is a blank spreadsheet.

How do I get this data to be exported into a the same named worksheet regardless if it is a new or existing
spreadsheet?

Thanks again


I initialize tempSO with the Sales Order number in a previous bit of code

' If outfile is set to SAME then title the spreadsheets with the order number
    If Out_File = "C:\Stuff\SAME" Then
        Out_File = "C:\Stuff\" & tempSO & ".xlsx"
        tempSO = 0
    End If
    
    With objXL
        .Visible = True
   
        If tempSO <> 0  Then        ' if TempSO = 0 then the excel file is to be named with the SO #
            Set objWkb = .Workbooks.Open(Out_File)
        Else
            Set objWkb = .Workbooks.Add
             objWkb.saveas Out_File, 51
        End If
        On Error Resume Next
        
         Set objSht = objWkb.Worksheets("works")         'RSP
        objWkb.Worksheets("RSP").Activate

        lngLastRow = objSht.Cells.Find(What:="*", _
                            After:=objSht.Range("A1"), _
                            LookAt:=2, _
                            LookIn:=-4123, _
                            SearchOrder:=1, _
                            SearchDirection:=2, _
                            MatchCase:=False).Row
        End With

         lngLastRow = lngLastRow + 1

        With objSht
            .Range("A" & lngLastRow).CopyFromRecordset rs1
            With .Rows(lngLastRow & ":" & lngLastRow + rs1.RecordCount)
            End With
         End With

     objWkb.Save
 
    objWkb.Close
    objXL.Quit

    Set objXL = Nothing
    Set rs1 = Nothing

 

[combo]

In second part of:

If tempSO <> 0  Then        ' if TempSO = 0 then the excel file is to be named with the SO #
    Set objWkb = .Workbooks.Open(Out_File)
Else
    Set objWkb = .Workbooks.Add
    objWkb.saveas Out_File, 51
End If

you need to rename sheet(s). You may test how many sheets excel creates in new workbook, it's parameter that user can individually set. If necessary, add some:

If tempSO <> 0  Then        ' if TempSO = 0 then the excel file is to be named with the SO #
    Set objWkb = .Workbooks.Open(Out_File)
Else
    Set objWkb = .Workbooks.Add
    With objWkb
        .Worksheets(1).Name="RSP"
        If .Worksheets.Count<2 Then .Worksheets.Add
       .Worksheets(2).Name="works"
        .SaveAs Out_File, 51
    End With
End If

combo

 

[irethedo]

Wow, that works really great... thanks Combo

Just one other question.
Because this is s new spreadsheet, is there a way to insert the field names of my
access table on the top line like a header of the spreadsheet?
Right now it comes across as a blank top line

Thanks again!!!

 

[combo]

rs1(Fields(0).Name returns first field name.
Loop from 0 to rs1.Fields.Count-1 and assign to objWkb.Worksheets(1).Cells(1,1) - objWkb.Worksheets(1).Cells(1,rs1.Fields.Count).

combo

 

[irethedo]

Thanks Combo

I am getting a syntax error on:

        rs1(Fields(0).Name returns first field name.
        Loop from 0 to rs1.Fields.Count-1 and assign to objWkb.Worksheets(1).Cells(1,1) - objWkb.Worksheets(1).Cells(1,rs1.Fields.Count).

 

[Andrzejek]

Combo did not give you code - he gave you the way to do it.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[irethedo]

I was wondering about that, but didn't catch on to his format...

I think I can figure out the looping and gathering of the data field names but I am not sure how to assign each of these to a cell...


This is what i have so far...

  strSql = "Select * from NW_Excel_tbl order by Comp_Name"
    Set rs1 = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
    Startval = 0
    Do While Startval < (rs1.Fields.Count-1)
	somefield = rs1(Fields(StartVal).Name
' then some how I assign somefield  to worksheet(1).Cells(1,StartVal)
Loop

 

[Andrzejek]

This is how I do it:

Dim i As Integer

For i = 0 To rs1.Fields.Count - 1
    xlSheet.Cells(1, i + 1) = rs1.Fields(i).Name
Next
xlSheet.Range("A2").CopyFromRecordset rs1

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

The method I have used for years to get data from Access or any other database into Excel, is to query directly from Excel via MS Query, so the Query Table need only be Refreshed on demand. Its always on the sheet, exactly with the formatting you intend.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}