Same folder same path, but ...

[kupe]

The spreadsheet I export with

DoCmd.TransferSpreadsheet acExport,

goes to the same folder that contains the db. The path is a long one.

Is there a way to use an abbreviated path please? For instance, an ellipsis takes Dreamweaver to the current folder.

(The books aren't helping because I cannot find the right term for this process, sorry.)

 

[dcx693]

Look at this post: Determining Own Filename

 

[kupe]

Thanks, dcx693. But where will this code go?

The code in this instance is,

DoCmd.TransferSpreadsheet acExport, 8, "qryQuery", "t:\com\OnLine\Fred\dcx693.xls", True, ""

Would you code

DoCmd.TransferSpreadsheet acExport, 8, "qryQuery", "CurrentProject.dcx693.xls", True, ""

You'll see I'm a bit puzzled or lost, or both. Good to hear form you.

 

[dcx693]

Let's say your Excel file name is dcx693.xls

Dim strXlFile as String
Dim strPath as String

    strXlFile="dcx693.xls"
    strPath=Left(CurrentDb.Name,Len(CurrentDb.Name)-Len(Dir(CurrentDb.Name)))

    DoCmd.TransferSpreadsheet acExport, 8, "qryQuery", strPath & strXlFile, True, ""

 

[kupe]

Looks very good, dcx693. Thanks very much. Cheers

 

[kupe]

And working well, thanks dcx693.

I can see that if the file name is changed regularly, this might be a handy code. I wonder what its major use is, please? I mean, when would programmers always use this?

 

[dcx693]

Programmers always try to recycle and repurpose code. Why reinvent the wheel twice? I always variables if I have a particularly long string or numeric - it improves the readability of the code. I always use a variable when I use a constant string or number more than once in code - that way you can just change the value in one place. Generally, be as non-specific as possible, if that makes sense.

 

[kupe]

I see, yes, that makes good sense, thank you. I've been thinking of keeping a collection of code to go back to, which I often do - if I haven't forgotten where the particular piece is now.

Keeping a readily accessible collection must be what you experienced people do. I'll follow suit. Many thanks for the guidance.