Solved Output to Excel

[PaquettePaul]

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]

Just a guess, but what about the user's permission to the folder? Try outputting to a different folder with write/create permission.

 

[PaquettePaul]

Nope. Like i said, the pdf’s worked fine and this is on my laptop. But good effort

 

[theDBguy]

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]

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]

What is gblQt meant to contain?
Unlikely to stop creating a file, but I am curious as to the suffix?

 

[Minty]

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]

what if you remove gblQt from the expression:

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

 

[PaquettePaul]

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]

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]

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]

As noted in the first post, I did supply a file name extension as .xlsx.

 

[Gasman]

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]

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.

 

[Gasman]

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.

Fair do, I missed that. Sorry.