Transfer new data to Excel not working

[dmgg]

I am trying to transfer data from Access (latest version) to Excel. The transfer has worked for a long time and works OK until I add two fields to the source query (q_Excel_Export_2). The message I get is shown below. I just can't figure it out.

The message:
RUN TIME ERROR '3420'
OBJECT INVALID OR NO LONGER SET

The stop occurs at the third line.



Dim exfile As String
exfile = CurrentProject.Path & "" & .ExcelFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "q_Excel_Export_2", exfile, -1

Any help much appreciated

[alansidman]

An alternative is to import the query to Power Query and then close and load the data to excel.

Power Query Access database connector - Power Query | Microsoft Docs

[dmgg]

The xfer of data to excel is only a small part of the program. This is an item for sailboat downwind phrf (seconds per mile) ratings. Lots of other things, so it has to be just select the boat and proceed.. It is just one of the items on the form. I will look at the data and see if possible to use.
Thanks much for replying so soon.

[Welshgasman]

Anything actually in ExcelFile?
Walk through your code inspecting variable values.
Debug.Print them if need be.

[Gicu]

CurrentProject.Path does not include the backslash so unless you have it in the textbox that has the file name you need to include it:

Dim exfile As String
exfile = CurrentProject.Path & "\" & Me.ExcelFile 'assumes you have a textbox on the form to hold the Excel file name
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "q_Excel_Export_2", exfile, True

Cheers,

[dmgg]

Anything actually in ExcelFile?
Walk through your code inspecting variable values.
Debug.Print them if need be.

The code has been working for a long time with lots of data sent from Access to Excel. Problem comes from adding two new fields. I just can't make it work.

[dmgg]

I do have the backslash included. The thing that is driving me crazy is that the program has worked for years. Somehow, adding two fields to Access just won't work! I have changed the query that exports the data, but still won't work.

[ssanfu]

OK, if the export worked until you modified the query, it has to be something related to the query.
You have not posted the SQL of the query BEFORE the change, nor the SQL of the query AFTER the change.

What are the two "fields" (columns) added to the query? Did you add two fields to a table and include the new fields in the query?

If you open the query "q_Excel_Export_2", do you get the expected results?

[dmgg]

Thanks for reply.
I did check and the two new fields are there and exactly where they are supposed to be.

The alarm message is:
ERROR 3027
Cannot Update. Data Base or Object is read only.

When click on error message, takes me to this portion of the code that exports data to excel.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "q_Excel_Export_2", exfile, -1

Driving me crazy. Without the two new fields, it has worked perfectly for years. I am able to manually export to a new excel file. I will try to append and get back to you.

[ssanfu]

If you open the query "q_Excel_Export_2", do you get the expected results?


Please post the SQL of the query BEFORE the change and the SQL of the query AFTER the change.

[dmgg]

If you open the query "q_Excel_Export_2", do you get the expected results?


Please post the SQL of the query BEFORE the change and the SQL of the query AFTER the change.

Here are the two. Hope they show you something. The excel file with or without the new fields works fine with the original query. Sorry I took so long to respond. Had some other things I had to do. Appreciate your help.

Query with two more fields that won’t append to excel file. Two added fields are
SELECT TblBoatData.BOAT_NAME, TblBoatData.BOAT_TYPE, TblBoatData.i, TblBoatData.hdsail AS [LP_%], TblBoatData.LP, TblBoatData.HSGIRTH AS HS_Girth, TblBoatData.ISP, TblBoatData.J, TblBoatData.JSP, TblBoatData.P, TblBoatData.E, TblBoatData.hb AS MGT, TblBoatData.MGU, TblBoatData.MGM, IIf([SYM_SPIN]="yes","Y","N") AS [sym?], TblBoatData.SL, TblBoatData.SSMG, TblBoatData.SSF, TblBoatData.jsp AS SPL, IIf([ASYM_SPIN]="YES","Y","N") AS [Asym?], TblBoatData.SLU, TblBoatData.SLE, TblBoatData.ASMG, TblBoatData.ASF, TblBoatData.SPRIT AS SpritLength, TblBoatData.[Articulate Spinnaker] AS Artic, TblBoatData.PY, TblBoatData.EY, 0 AS hdsa, 0 AS sa_M, 0 AS SA_s, 0 AS SA_a, TblBoatData.LOA, TblBoatData.LWL, TblBoatData.Maxbeam AS Beam, TblBoatData.Max_draft AS Draft, TblBoatData.Displacmt AS Disp, T_DW_BallastFactor.BallastMovType AS MovBallast, TblBoatData.profile, TblBoatData.prop_type AS Prop, TblBoatData.comshift, TblBoatData.LH, TblBoatData.BO, TblBoatData.X, TblBoatData.h, TblBoatData.SO, TblBoatData.Y, TblBoatData.irc_wt AS IRCwt, TblBoatData.ORR_L AS ORRratedL, TblBoatData.disp_orr AS DispORR, TblBoatData.Com_l, TblBoatData.comdispl AS Com_Displ, TblBoatData.IDboat
FROM TblBoatData INNER JOIN T_DW_BallastFactor ON TblBoatData.BALAST_MOV = T_DW_BallastFactor.IDballast
WHERE (((TblBoatData.IDboat)=[Forms]![FrmMemberScreen].[form].[IDboat]));

Query that works
SELECT TblBoatData.BOAT_NAME, TblBoatData.BOAT_TYPE, TblBoatData.i, TblBoatData.hdsail AS LP, TblBoatData.ISP, TblBoatData.J, TblBoatData.JSP, TblBoatData.P, TblBoatData.E, TblBoatData.hb AS MGT, TblBoatData.MGU, TblBoatData.MGM, IIf([SYM_SPIN]="yes","Y","N") AS [sym?], TblBoatData.SL, TblBoatData.SSMG, TblBoatData.SSF, TblBoatData.jsp AS SPL, IIf([ASYM_SPIN]="YES","Y","N") AS [Asym?], TblBoatData.SLU, TblBoatData.SLE, TblBoatData.ASMG, TblBoatData.ASF, TblBoatData.SPRIT AS SpritLength, TblBoatData.[Articulate Spinnaker] AS Artic, TblBoatData.PY, TblBoatData.EY, 0 AS hdsa, 0 AS sa_M, 0 AS SA_s, 0 AS SA_a, TblBoatData.LOA, TblBoatData.LWL, TblBoatData.Maxbeam AS Beam, TblBoatData.Max_draft AS Draft, TblBoatData.Displacmt AS Disp, T_DW_BallastFactor.BallastMovType AS MovBallast, TblBoatData.profile, TblBoatData.prop_type AS Prop, TblBoatData.comshift, TblBoatData.LH, TblBoatData.BO, TblBoatData.X, TblBoatData.h, TblBoatData.SO, TblBoatData.Y, TblBoatData.irc_wt AS IRCwt, TblBoatData.ORR_L AS ORRratedL, TblBoatData.disp_orr AS DispORR, TblBoatData.Com_l, TblBoatData.comdispl AS Com_Displ, TblBoatData.IDboat
FROM TblBoatData INNER JOIN T_DW_BallastFactor ON TblBoatData.BALAST_MOV = T_DW_BallastFactor.IDballast
WHERE (((TblBoatData.IDboat)=[Forms]![FrmMemberScreen].[form].[IDboat]));

[CJ_London]

The only object involved with transferspreadsheet is the query (don't know if transferspreadsheet invokes objects behind the scenes)

time for some experimenting to determine what is causing the error - remove the 2 columns from q_Excel_Export_2 and run your code - does it run OK or do you get an error

assuming no error, add back one of the columns and repeat

assuming no error remove the first column and add the other column

assuming no error add back the first column (i.e. back to the original)

at least should determine if the error is actually due to the two fields or something else.



Other things to try
- compact/repair
-decompile/recompile

-presume you have option explicit at the top of all modules and code compiles without error

-you don't need the -1 in the end parameter - the value is ignored when exporting

[davegri]

I tried defining and using a string with name ending in _% and got errors even with brackets.

Try changing TblBoatData.hdsail AS [LP_%] to TblBoatData.hdsail AS LP_Pct

[CJ_London]

I tried defining and using a string with name ending in _% and got errors even with brackets.

one more for the pot - ideally should never use non alphanumeric characters in field names, particularly those that can be used by sql itself

[dmgg]

I tried defining and using a string with name ending in _% and got errors even with brackets.

Try changing TblBoatData.hdsail AS [LP_%] to TblBoatData.hdsail AS LP_Pct

I finally got a chance to try it. Unfortunately, still won't work.