Solved Output to Excel (1 Viewer)

PaquettePaul

Member
Local time
Yesterday, 21:55
Joined
Mar 28, 2022
Messages
107
I have a configuration field which identifies the path where pdf reports are to be stored. I join the path, a file name, and a pdf extension, use the OutputTo command and it works as expected behind the scene.

However, when I try to do the same with Excel, it rejects the request. If I leave out the file parameter, the OS asks for the file name and location. But it stores the excel spreadsheet without problem. gblQt is constant equal to “

stFileName = "Flight Time Summary By Date"
gblText = gblQt & gblDirectoryPath & "\" & stFileName & ".xlsx" & gblQt
MsgBox gblText
DoCmd.OutputTo acOutputQuery, "xclACFlightMinQ", acFormatXLSX, gblText

The message I get back is:

Microsoft Access can't save the output data to the file selected

i put the msg box code in to check the contents of the string, but it appears the same as when I do a pdf report.

An6 thoughts?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:55
Joined
Oct 29, 2018
Messages
21,358
Just a guess, but what about the user's permission to the folder? Try outputting to a different folder with write/create permission.
 

PaquettePaul

Member
Local time
Yesterday, 21:55
Joined
Mar 28, 2022
Messages
107
Nope. Like i said, the pdf’s worked fine and this is on my laptop. But good effort 😃
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:55
Joined
Oct 29, 2018
Messages
21,358
Nope. Like i said, the pdf’s worked fine and this is on my laptop. But good effort 😃
All I can do is guess. How about trying it with a file name (and folder name) without spaces? Just a thought...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:55
Joined
Feb 28, 2001
Messages
27,001
but it appears the same as when I do a pdf report.

That quote says the name looks the same as when done another way, but "looks the same" can be misleading if you are using a proportional font that allows kerning. The ultimate test is to examine the name using a non-proportional font like one of the Courier variants or Lucida Console. There, if you have an embedded space, it will stand out like a sore thumb. You CAN create filenames with spaces in them, but they usually have to be defined in quotes to be read correctly.
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:55
Joined
Sep 21, 2011
Messages
14,048
What is gblQt meant to contain?
Unlikely to stop creating a file, but I am curious as to the suffix?
 

Minty

AWF VIP
Local time
Today, 01:55
Joined
Jul 26, 2013
Messages
10,355
Does a file with the same name already exist, and is it open?
That will cause an error, although possibly not the one you are describing.

For testing just hard code a path to

Currentproject.Path & "\FlightTimeSummaryByDate.xlsx"

This will save it where your database is stored so no potential permissions problems.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:55
Joined
May 7, 2009
Messages
19,169
what if you remove gblQt from the expression:

gblText = gblQt & gblDirectoryPath & "\" & stFileName & ".xlsx" & gblQt
 

PaquettePaul

Member
Local time
Yesterday, 21:55
Joined
Mar 28, 2022
Messages
107
gblQt is a global constant that has a value of “. So rather than putting in “”” which is weird looking to me, I just put in the constant.
i had to use the leading and trailing quotes because my File path and name for storing pdf files fails without it. (Or did, aargh)

I went to the basics with a bunch of these suggestions.
- first, D:testme as the path without spaces or quotes or a complicated directory path, worked fine
- next, D: testme pls, worked fine (so, spaces not an issue)
- next, tried the whole thing over again without the leading and trailing double quotes, and the blighter worked
- next, tried the same with pdf, and that worked also (confused by this one but ok, whatever)

so Arnelgp had the right answer, not that the rest of the suggestions were not appreciated.
and I did try creating the same spreadsheet again and it automatically overwrote the previous one, which is what I wanted.
Thanks again for all the points, assumptions are not always good and trying suggestions, even those “that cannot be the answer”.
My Flight School app would not be where it is without you guys.
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:55
Joined
Sep 21, 2011
Messages
14,048
Well I did ask in post #6? :(
Never thought someone would try and create a file with no suffix, at least not with a suffix that a program needs to recognise?
At least now we know you cannot create a file with that naming comvention. :)
 

PaquettePaul

Member
Local time
Yesterday, 21:55
Joined
Mar 28, 2022
Messages
107
Sorry, my bad, Yes, the filenames I used had the extension and should have been noted as D:testme.xsls and D:testme pls.xsls in the text that w The leading and following double quotes that I used were the issue.
 

PaquettePaul

Member
Local time
Yesterday, 21:55
Joined
Mar 28, 2022
Messages
107
As noted in the first post, I did supply a file name extension as .xlsx.
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:55
Joined
Sep 21, 2011
Messages
14,048
As noted in the first post, I did supply a file name extension as .xlsx.
No you did not. :(
You supplied .xlsx & gblQt, which is why I asked what was in gblQt, as I could not understand why you would add another suffix?

As for the quoted text above, that could just as easily be a full stop at the end of the sentence?
However as I said previously, now we know you cannot end a filename with a full stop/period. Either supply a suffix or nothing.
You do not need a suffix, but if you want a program to open the file by default, you do.
 

PaquettePaul

Member
Local time
Yesterday, 21:55
Joined
Mar 28, 2022
Messages
107
Not to get into this more than it needs. I said in the first post that gblQt was a constant set to “. See second paragraph.
 

Users who are viewing this thread

Top Bottom