Every time I go to output a form to excel, it looks like it has saved export settings on how the outputted form should look and what it is named. Where can I find these settings?
Thanks,
Nick
Every time I go to output a form to excel, it looks like it has saved export settings on how the outputted form should look and what it is named. Where can I find these settings?
Thanks,
Nick
You don't save forms to excel.
do you mean,save the datasheet ON the form to excel?
if so, docmd.transferspreadsheet....
Yes, it is filtered data from data on the first form. I will try the transfer spread sheet method.
Thanks,
Nick
OK, I tried the transfer spreadsheet method and it did not work for me. It said that the range I specified already existed. I guess what I am looking for is basically when the data from the form is outputted to xls. The new column that I have added is never in the right place. I also have a datasheet view as a button and all I had to do was drag and drop the column to the right area. I just want to be able to adjust the outputted columns in the excel worksheet but it always puts it by itself and you have to move the column each time.
Thanks,
Nick
You have to supply a query with the columns ordered the way you want.
the form is only available to the user ,not to the transfer.
I tried the transfer again and it just thinks when the button is clicked and does nothing.
This is what I have in my macro:
Action: TransferSpreadsheet
Transfer Type: A-B-Detail
Spreadsheet Type: Microsoft Excel 8-9
Table Name: A-B-Detailed
File Name: ABDetailed
Has Field Names: No
Range:
Put the full file path that you want the file to go to in the File Name argument.
Refer to this for each argument: https://msdn.microsoft.com/en-us/lib.../ff844793.aspx
Ok, I am able to import the access query to excel but It continues to not import the new column. I have checked all the queries and tables that I have built for this form and they seem to have the correct structure. I also tried importing the table using excel and when I imported the query I wanted, it was still not correct but when I look at the query in access it shows as correct. Am I missing something?
Thanks for your all your help,
Nick
So is "A-B-Detailed" the name of your Table or Query?
And the new field displays in there?
Delete the old file first before trying running it again to ensure that you are getting a new file.
It almost sounds like it is remembering some old export settings. Maybe try re-creating the macro and see if that helps.
Yes, AB detailed is the query. Yes the new field displays in the ab details query. I just decided to use the previous export technique that used code to output the form. I recreated the same thing with a macro assigned to a new button and it still incorrectly copied over the structure. It would always take the new column and put it all the way to the right in a unnamed column. So we need to rearrange that column to the correct area but I am not sure how to change those. I believe there is some type of export settings that is doing this to me?
Here is the code
This is a form created from another form with a filter on the previous form depending on where I double clicked on a different query in the form.Code:Private Sub Command68_Click()On Error GoTo Err_Command68_Click Dim stDocName As String stDocName = Me.Name DoCmd.OutputTo acForm, stDocName, "MicrosoftExcel(*.xls)", "C:\DBList.xls", True, "" Exit_Command68_Click: Exit Sub Err_Command68_Click: MsgBox Err.Description Resume Exit_Command68_Click End Sub
Thanks,
Nick
Are they in the correct order in your Query?
I would expect it to export in the same order that they are in the query that you are exporting.
Note: You can also export the Query using "DoCmd.OutputTo" (an alternative to using TransferSpreasheet).
See: http://access-excel.tips/access-vba-...tputto-method/
Yes, it is in the correct order in the query.
I have figured it out. When I went to originally output the form itself. When I right click in the form there is a tab order button that you click.This contains the custom order that access exported to excel. Once I changed that order in the detail section, it outputted it correctly to excel.
Thank you for all your help,
Nick