Select a Folder for Access 2007 Table Export

  • Thread starter Thread starter Erick C
  • Start date Start date
E

Erick C

Hello again everybody.
I have a new problem that I cannot figure out. I want to set up a
button to open a dialog box that will allow a user to select the
folder that a table will be exported to. I have copied the code from
http://www.mvps.org/access/api/api0002.htm into its own module (I am
already using the api0001 code). I then used a code from Ken Snell
for the button. The code is below:

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the High Level file will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

strFile = " FormatDateTime(Now(),4) &”High Level Report.xls"

strTable = "High Level Summary by Subinventory"
strPathFile = strPath & "\" & strFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile


I did modify the strFile because I wanted the date to pull in as part
of the file name.
When the button is pressed I get a dialog box that opens, but the file
that gets created in the folder that is chosen is all messed up.
There is just an empty file with no .xls or anything on the end of the
file, a name of 11, and the file size is 0.
What did I do wrong? Which code is causing my issues?
Any help would be greatly appreciated.

Erick
 
Hi Erick,

FormatDateTime(Now(),4) returns hh:mm. The colon is not a valid
character for a file name. I would suggest something like:

Format$(Now(), "hh\-mm")

Clifford Bass
 
Hi Clifford -
Thank you for the response. I actually do not need the time, all I
really need is the year, month and day; like a medium date format (10-
Jun-09). How can I modify your expression to meet this criteria?
 
Hi Erick,

You could do the following. Note that I would suggest using only
digits and doing it in yyyy/mm/dd order so that your files will sort
chronologically when sorted by name.

Format$(Date(), "yyyy-mm-dd")

However, to do it in the format you specified use:

Format$(Date(), "dd-mmm-yy")

Clifford Bass
 
Hi Erick,

Oh yes... For medium date you can also use:

Format$(Date(), "Medium Date")

This should follow the regional settings of the computer.

Clifford Bass
 
Sorry, I spoke too soon. There is another problem that has popped up
that I do not know if it can be fixed or not. I cannot export to a
shared drive. When I select a local folder, like My Documents, the
file exports just fine. If I select one of my shared work drives then
I get a Runtime Error 2220, saying it can't open the file. I don't
know what to do with this error, and unfortunately I ave a bunch of
files that are being exported, it is a bit difficult to manage from a
local folder.
Help, please!
 
Hi Erick,

Basic questions: Do you have write access to the network folder? How
long is your total path name when writing to the network folder, including
the file name? There is a limit of around 260 characters. In other words,
if you do the export to a spreadsheet manually, does it work?

Clifford Bass
 
Clifford -
I double checked the write access and it is fine. I can export the
file manually to the folders. There are only about 20-30 characters
in the path name, so no issues there.
 
Hi Erick,

No clues from that. Okay, so maybe it is something about the path and
file name? Add a MsgBox line after you set strPathFile.

strPathFile = strPath & "\" & strFile
msgbox strPathFile

What does it show? Also, does that file already exist?

Clifford Bass
 
The path that shows up is:
Y:\\"file name".xls
The file name shows up in the correct format that we modified before.
The file does not exist anywhere on the shared drive.
 
Hi Erick,

I would suspect the double backslash may be causing the problem. You
could either check for its existence at the end of the path or use the
FileSystemObject's BuildPath method.

' Requires reference to Microsoft Scripting Runtime (Tools, References)
Dim fso As New FileSystemObject

strPathFile = fso.BuildPath(strPath, strFile)

Also, I presume that where you have "file name" that that is only to
indicate the constructed file name and that the actual file name does not
include the quotes.

Clifford Bass
 
Hi Clifford -
I am guessing you are correct, since I do not get the double backslash
when exporting to the local hard drive.
You are correct in your assumption, "file name" only indicates the
constructed name and there are no quotes in the name.
I turned on the Microsoft Scripting Runtime. So, did I add the info
you provided into the code correctly?

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
Dim fso As New FileSystemObject

strBrowseMsg = "Select the folder where the new EXCEL file will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

strPathFile = fso.BuildPath(strPath, strFile)

strFile = Format$(Date, "Medium Date") & " High Level Report.xls"

strTable = "High Level Summary by Subinventory"
strPathFile = strPath & "\" & strFile
msgbox strPathFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile
 
Hi Erick,

No, not quite correct. Try this change between the End If and the
MsgBox:

End If

strTable = "High Level Summary by Subinventory"
strFile = Format$(Date, "Medium Date") & " High Level Report.xls"
strPathFile = fso.BuildPath(strPath, strFile)
MsgBox strPathFile

Clifford Bass
 
Clifford -
Sorry, I am still learning. I just kind of stick stuff all over the
place.
I made the new changes and everything is working now!
You are the best! Thank you again for all of your help!

Erick
 
Hi Erick,

Good, glad to hear it is working. You are welcome.

At a core level, VBA programs run sequentially, one step at a time. So
when you add code in, you have to put it in where it makes sense in the
sequence of whatever else is happening.

Clifford Bass
 
Back
Top