Select a Folder for Access 2007 Table Export

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
 
C

Clifford Bass

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
 
E

Erick C

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?
 
C

Clifford Bass

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
 
C

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
 
E

Erick C

Clifford -
Thank you for the update. The export is working fine now!
Thank you again.
 
E

Erick C

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!
 
C

Clifford Bass

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
 
E

Erick C

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.
 
C

Clifford Bass

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
 
E

Erick C

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.
 
C

Clifford Bass

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
 
E

Erick C

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
 
C

Clifford Bass

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
 
E

Erick C

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
 
C

Clifford Bass

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select Folder and Import from all Excel files in Folder 4
Importing Excel spreadsheets from a folder 5
Problem exporting multiple tables into different worksheets in oneworkbook 3
Word macro that can call cells out of Excel for a find and replace 0
Import password protected excel files 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Import password protected excel sheets 0
Help Importing Multiple Text Files into Different Tables 7

Top