Generalizing my VBA transfer of an MS Access table to MS Excel

[Lou_Reed]

Okay, here you are. Sorry about not uploading the correct file.

Respectfully,



Lou Reed

[John_G]

I do not want the first row of the table of data to be written to the Excel worksheet - no matter what line it is on. That line contains no data only columns names.

That's not true for Access. The field names (column names) are not a row in the table; they are displayed as column headers when you open the table.

That said, I don't think you can export to Excel without the field names being included (according to a google search). You need some VBA to open the resulting Excel file and delete that row.

But why are you even having to ask this question? You already have a perfectly good export routine as part of the form ExportAccessTabletoExcel. If you don't want to include the field names, comment out the code that writes them to the Excel spreadsheet.

[ssanfu]

Or add a Boolean variable to have the option to include the field names or not......

[Lou_Reed]

That is the issue here. I need to know how to modify the code so that the top row of the table (which only contains column titles) does not go over to Excel. That row is not needed, the Excel work sheet that receives the MS Access export already has the column titles in it. There is no need for a second row.

I know little about recordsets and that is why I am asking how to modify the code.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

"But why are you even having to ask this question? You already have a perfectly good export routine as part of the form ExportAccessTabletoExcel. If you don't want to include the field names, comment out the code that writes them to the Excel spreadsheet."

Sorry I do not know how to put the above quote in correctly so I will just put in there like it is now.


I do not know how to comment out the code in ExportAcessTabletoExcel. That is my question. I believe that it deals with recordsets and that is why I am posing the question. I know very little about recordsets.

I could put in VBA code in the Excel sheet that receives the MS Access tables data (and fields names), but I am interested in how to change the VBA code in ExportAcessTabletoExcel.

Any help appreciated. Thanks n advance.

Respectfully,

Lou Reed

[ssanfu]

<snip> I do not know how to comment out the code in ExportAcessTabletoExcel. That is my question.

Hmmmm. I thought you wrote the code.

Look at the following. Do you know which line is commented out and how?

    ' FieldCount contains the number of fields in the recordset
    '
    FieldCount = rs.Fields.Count

Or do you mean you can't read the code and understand what it does??
Do you understand what this code does?

    '
    ' first row of the spreadsheet contains the fieldnames, making it a header row
    '
    For J = 1 To FieldCount
        ws.Cells(rownum, J).Value = rs.Fields(J - 1).Name   'Use j-1 because recordset field index is zero-based
    Next J

I am serious about these 2 questions.
After being a member for 2 years, I would expect you to know how to comment out lines of code; that is VBA programming 101.


Or maybe I totally misunderstood your comments.....





There are what I consider errors in the code, so I rewrote the sub

Private Sub ExportTabletoExcel_Click()
    On Error GoTo ExportError

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim xl As Excel.Application
    Dim FieldCount As Integer, J As Integer
    ' Dim fld As Field      '<- not used in code
    Dim ws As Worksheet
    Dim rownum As Long

    Set db = CurrentDb


    Set xl = New Excel.Application
    xl.Visible = False
    xl.Workbooks.Open ("C:\Users\\james.yunker\Desktop\testbook.xlsx")

    Set ws = xl.ActiveWorkbook.Sheets.Add
    ws.Select

    Set rs = db.OpenRecordset(Me!TableList)
    rownum = 1

    rs.MoveFirst

    '
    ' FieldCount contains the number of fields in the recordset
    FieldCount = rs.Fields.Count

    '########
        ' first row of the spreadsheet contains the fieldnames, making it a header row
       'comment out the next 3 lines if you *don't* want field names written to Excel spreadsheet row 1
        For J = 1 To FieldCount
            ws.Cells(rownum, J) = rs.Fields(J - 1).Name   'Use j-1 because recordset field index is zero-based
        Next J
    '########
    
    '
    ' Now copy the data from the recordset to the Excel spreadshheet rows...
    Do While Not rs.EOF
        rownum = rownum + 1
        For J = 1 To FieldCount
            ws.Cells(rownum, J) = rs.Fields(J - 1)  ' Use j-1 because recordset field index is zero-based
        Next J

        rs.MoveNext
    Loop

    MsgBox "Finished"

Export_Exit:
    On Error Resume Next
    xl.ActiveWorkbook.Close (True)
    xl.Quit
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set ws = Nothing
    Set xl = Nothing

    Exit Sub

ExportError:
    If Err.Number = 3078 Then
        MsgBox "The selected table " & Me!TableList & " is not valid"
    Else
        MsgBox "Error " & Err.Number & ":   " & vbCrLf & Err.Description & vbCrLf & "occurred on export of table " & Me!TableList
    End If

    Resume Export_Exit

End Sub

[Lou_Reed]

I very much appreciate your help. I guess that I do like the cynicism or sarcasm. The issue here is and always has been recordsets. That is what do not understand.

To answer one of your questions yes I did write the code for the first iteration. I could have left it there and it would have satisfied the sponsor, I though maybe this needs to be generalized to not make it so specific to one type of table. In other words generalized.

I though that was a good things to do. I really do not understand recordsets, but of course I understand how to comment out VBA code. Please do not be so patronizing.

I do not understand how to get the table in MS Access to export to MS Excel and leave off the first (top)row - the row with column titles on it.

As I said I already have that line on the Excel worksheet that is receiving the MS Access table's data,so why copy it over and add another line, an unnecessary line.

That was it and nothing more.

I am sorry if my tone is a little strident, but to be accused of not knowing basic VBA after as you said being a member for two years rankled me.

The question here is and always was the use copy, reading and writing of recordsets. That was the only questions thta I had. The only one.

Thanks for your help and please understand that just because someone does not understand it as well as you is not call this type of colloquy.

Respectfully,

Lou Reed

[ssanfu]

I very much appreciate your help. I guess that I do like the cynicism or sarcasm. <snip>

Apologies....I was not meaning/intending to be cynical, sarcastic or patronizing.
------------------------------------------------------------------------------

In Post#50, you wrote

<>snip
I do not know how to comment out the code in ExportAcessTabletoExcel.

I was unbelieving that after being a member for 2 years and having 1500 posts that you would not know how to comment out lines of code (your words).


Now you say

The issue here is and always has been recordsets. That is what do not understand.

This is a different matter.


My understanding of recordsets:
When you open a table, you have a record set (technically a Data Set). When you open a saved query, you have a record set. I also call a query a "Virtual table" because a query can be composed of one or more tables that "go away" when the query is closed.
When you open a query (saved or SQL) in code, you have a "recordset" object. You have a line

Set rs = db.OpenRecordset(Me!TableList)

The recordset object "rs" contains the records from the saved query Me!TableList. Once in memory, you can manipulate the data in the recordset (as you do).

-------------------
You do a very good job of commenting your code (I usually have to go back and add comments )
I've watched your understanding grow, but it seems like you ask questions without trying to solve the problem yourself.

For instance:
your code "ExportTabletoExcel_Click" is writing a header and data to an Excel spreadsheet. The code is overwriting line 1 in the spreadsheet.
How to stop the code from writing to Row 1 in the SS?

There is a comment line

    '
    ' first row of the spreadsheet contains the fieldnames, making it a header row
    '

Then 3 lines of code.

    For J = 1 To FieldCount
        ws.Cells(rownum, J).Value = rs.Fields(J - 1).Name   'Use j-1 because recordset field index is zero-based
    Next J

Wouldn't it make sense to comment out the 3 lines that write the field names to the spreadsheet???


-------------------
More and more it seems (to me) that you post a question because you don't take the time to try and "think" your way through the problem.
I mentally step through each line of code and determine what it does and if that is what I want it to do, making changes if I need to until the code works.


-------------------
It seems like, at times, you are a beginning intermediate. I don't know you or you abilities with VBA, but you do things (actually don't do) in code that I disagree with.
One thing is this - there is a "rule" I learned many, many years ago that goes like this:

"If you open it, close it, If you create it, destroy it (talking about pointers)"

Example code:

    Dim db As DAO.Database   'object
    Dim rs As DAO.Recordset   'object
    Dim xl As Excel.Application   'object
    Dim ws As Worksheet   'object
    Dim rownum As Long
    Dim fld As Field, FieldCount As Integer, J As Integer
    
    Set db = CurrentDb  '<<Create it

    Set rs = db.OpenRecordset(Me!TableList)    '<<Open it
      
    Set xl = New Excel.Application   '<<Create it
    xl.Visible = False

    On Error GoTo ExportError

    xl.Workbooks.Open ("C:\Forum\LouReed\AllFiles\testbook.xlsx")    '<<Open it
    Set ws = xl.Worksheets("Expanded Tracker")   '<<Create it

Note: for the most part, anything with SET as the first word is creating an object. You know what "OPEN does.....

At the end of the code you should close/destroy the objects

   .
   .
    MsgBox "Finished"

    'clean up
    On Error Resume Next

    xl.ActiveWorkbook.Close (True)   '<< you closed it
    xl.Quit

    Set xl = Nothing               '<< you destroyed it
    Set ws = nothing               '<< you destroyed it

    rs.close                     '<< you close the recordset
    Set rs = Nothing               '<< you destroyed it

    Set dB = Nothing               '<< you destroyed it  '<< you DON"T "close" dB because you didn't open it. 

    Exit Sub

ExportError:
    If Err.Number = 3078 Then
    .
    .

-----------------------------------


Moving forward, I'll try to be more careful of how I respond............