INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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:

CODE -->

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 

RE: Exporting an access table into an exisiting excel spreadsheet versus a new spreadsheet

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

CODE -->

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:

CODE -->

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

RE: Exporting an access table into an exisiting excel spreadsheet versus a new spreadsheet

(OP)
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

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 

RE: Exporting an access table into an exisiting excel spreadsheet versus a new spreadsheet

In second part of:

CODE -->

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:

CODE -->

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

RE: Exporting an access table into an exisiting excel spreadsheet versus a new spreadsheet

(OP)
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!!!

RE: Exporting an access table into an exisiting excel spreadsheet versus a new spreadsheet

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

RE: Exporting an access table into an exisiting excel spreadsheet versus a new spreadsheet

(OP)
Thanks Combo

I am getting a syntax error on:

CODE -->

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). 

RE: Exporting an access table into an exisiting excel spreadsheet versus a new spreadsheet

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Exporting an access table into an exisiting excel spreadsheet versus a new spreadsheet

(OP)

Quote (Combo did not give you code - he gave you the way to do it. smile)


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...

CODE -->

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 

RE: Exporting an access table into an exisiting excel spreadsheet versus a new spreadsheet

This is how I do it:

CODE

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.

RE: Exporting an access table into an exisiting excel spreadsheet versus a new spreadsheet

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close