Transferspreadsheet & Path as String

[CCHDGeek]

I'm using Access 2007.

I'd like to create a dynamic string variable to the use in the Transferspreadsheet function in Access. The string will be the location/filename for the spreadsheet.

The string will look something like "\\Directory\Sub1\Sub2\Wk1\Excel.xls"

I'd like to create the string with code. Example:
DirStr = "\\Directory\Sub1\Sub2\Wk" & WeekNumber & "\" & "Excel.xls"
(where WeekNumber is an interger that represents a week as a number)

The code works fine when I do not include the WeekNumber variable. But when I do this (whether the directory exists or not). The WeekNumber variable is ignored altogether and the path isn't created correctly.

What am I doing wrong? I have a suspicion it's something simple and easy to fix.

Thanks in advance.

CC

[ADezii]

Are you creating each Folder beforehand or dynamically?

[CCHDGeek]

I haven't tried creating the folder dynamically. I've only tried creating the Wk1 folder prior to running the code. But I still get the same problems. I think it's something concatening "\" with the rest of the string for the file location.

[NeoPa]

Check out the value of WeekNumber before using it. When you know what the value is you'll have a much better understanding of your issue.

See below for some tips :

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[CCHDGeek]

It appears to be working now. But to follow-up, the value of Week is something like 201002 (year & weeknumber).

But thanks so much for your help. And the tips on troubleshooting!

[NeoPa]

No worries.

From your response, and your earlier posts, I suspect it's now working because there is a valid value in WeekNumber, whereas before (if my reading of the situation is correct) there was none. The code may need to check that the value is available before executing anything which depends on its availability.

Anyway, I'm pleased you've made progress :)