DoCmd.OpenTable without column headers? (1 Viewer)

madEG

Registered User.
Local time
Today, 12:01
Joined
Jan 26, 2007
Messages
309
In short: Is there a way to DoCmd.OpenTable without column headers?


In long...

I have MS Excel template with fairly complex header info I want the users to stay away from.


If I could wave a magic wand the user would "Press this accdb form's button, ctrl-A, ctrl-C, open the excel file, drop the cursor, ctrl-V, ctrl-S, then press X to close."



The problem is the user keeps forgetting to remove the table's field headers/column labels which hoses the data being submitted in the excel file.





Is there a way to docmd.opentable (to my linked sql server view) without copying the field names when I ctrl-C, or use the Copy button from the ribbon?



Thanks!
-Matt G.
 

Micron

AWF VIP
Local time
Today, 12:01
Joined
Oct 20, 2018
Messages
3,476
The transferspreadsheet function has an optional parameter that includes (or not) a header row, which would be the table field names if transferring from Access to Excel. It also provides for choosing a range to export the data to, so you could also start pasting on a row below any Excel header. Also, the link provided is to a site that only allows a few "free" looks at accepted solutions, so it might not be of much use. Maybe Google TransferSpreadsheet. You'll only get several million hits.
 

bastanu

AWF VIP
Local time
Today, 09:01
Joined
Apr 13, 2010
Messages
1,401
Micron, unfortunately as it is clearly stated in the MS documentation for the TransferSpreadsheet method the HasFieldNames argument does not apply for exporting, only for imports and linking (Quote:"When you export Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument." EndQuote):

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet

The link I provided does give some suggestions on how to delete the first row with field names if you scroll down enough..

Cheers,
Vlad
 

bastanu

AWF VIP
Local time
Today, 09:01
Joined
Apr 13, 2010
Messages
1,401
No worries, the transfer... methods are a bit tricky, not all the parameters are applying to all actions (range also only applies to import, not export).

Cheers,
Vlad
 

madEG

Registered User.
Local time
Today, 12:01
Joined
Jan 26, 2007
Messages
309
Thanks for the information folks! I'll take a look at the idea of pushing the info in the file myself, and removing the header record.

The headers should always end up on the same row, so this should be doable...

Thanks!
 

isladogs

MVP / VIP
Local time
Today, 16:01
Joined
Jan 14, 2017
Messages
18,186
I haven't needed to do this for several years but IIRC, I used an Excel template with the required column headings already place and the first two rows locked.

The export then added all the table data to the spreadsheet starting at row 2.
As row 2 was locked, that meant the field names were not copied across leaving a blank row and data starting at row 3.

I would need to check the details but the method worked perfectly.
 

Mark_

Longboard on the internet
Local time
Today, 09:01
Joined
Sep 12, 2017
Messages
2,111
In long...

I have MS Excel template with fairly complex header info I want the users to stay away from.

Could you set up a query that returns ONLY the records and fields you wish? If so, could you give the fields names that match your "Fairly complex header info"?
 

Users who are viewing this thread

Top Bottom