Top line if a db table is not being written to the Excel spreadsheet

[Lou_Reed]

In the two zipped files that are attached, I have one as a MS Access 2010 db file and the other is a Excel spreadsheet that the db will write a table to.

I noticed when I was testing that the first line of the table, the one that is for column titles is not being written.

My simpler questions is why is that?

Any help appreciated. Thanks in advance.



Respectfully,

Lou Reed

[pbaldy]

You've commented out the lines that would do it:

' 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

[Lou_Reed]

Okay, thanks that seems like it could work. I will give it a try.

Respectfully,

Lou Reed

[pbaldy]

No problem.

[Lou_Reed]

Okay, I think that worked. I cannot be sure. I uncommented the lines in questions form the previous post's response and changed the line where the program first starts to write to the Excel spreadsheet. But as you can see in the zipped file it left out row 5.

I am including the changed db and the spreadsheet after which it has written to it. It seems to have left ouf row 5. I think that is the line with the column names, but since I cannot see it, I am not sure.


What is going on here?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

Row 5 is there, it's been hidden (maybe started out that way?). Select rows 4-6, right-click and select Unhide.

[Lou_Reed]

Okay, I will give it a try. I have had this issue come up before. Is there a way to prevent this from happening in the first place?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

Is it visible in the template file? I don't see code offhand that would hide the row.

[Lou_Reed]

Yes, that worked.

Now if you look at the db and the Excel spreadsheet you will notice that I hardcoded the file name and extension type right into the VBA code in the
MS Access db form. That requires someone to create an appropriate file name and type before they open the MS access 2010 db for exporting the table to Excel spreadsheet.

They would also have to place the file in the correct directory since the path to it is also hardcoded in VBA.

I rather it not be so rigorous.

Is it possible to select it from a list of Excel spreadsheets and file extension types? Sort of like the application right above it on the form that exports an MS Access table to Excel.

As I said the combo box right above that lists all the tables in the db would be a appropriate choice. Any other suggestions would gladly be accepted.

Any help appreciated. Thank sin advance.

Respectfully,

Lou Reed


Any help appreciated. Thanks in advance.


Respectfully,


Lou Reed

[pbaldy]

ou can use File Dialog to let them find the spreadsheet they want to use. They'd have to input a sheet, or I think you may be able to default it to the first sheet.

[Lou_Reed]

I guess that I would like to use a browse button, but I am not sure of the syntax.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

Here's my template code:

    Dim fDialog As Object 'Office.FileDialog
    Dim varFile As Variant

    Set fDialog = Application.FileDialog(3) 'msoFileDialogFilePicker
    With fDialog
       .AllowMultiSelect = True
       .Title = "Please select files"
       .Filters.Clear
       .Filters.Add "Access Databases", "*.XL*"
       If .Show = True Then
          For Each varFile In .SelectedItems
             'DoCmd.TransferSpreadsheet acImport, 8, "master_table", varFile, True, ""
             Debug.Print varFile
          Next
       Else
          MsgBox "You clicked Cancel in the file dialog box."
       End If
    End With