Export Excel Sheet Path Combo

[SeadnaS]

Hi,

I have a button that exports a table to a new excel sheet. I have a text box next to it where the user enters the path that they want to save to. What i want is a combo box that lists drive paths to save to so that they can select oh i don't know, desktop or a folder on the c drive or a network drive or somewhere and they don't have to type in C:\blah blah blah... basically like when you click save as on any program, I want a combo box like that.

All help appreciated!

Thanks.

 

[Gammachaser]

If you don't need to limit where the folder is (i.e. it doesn't specifically have to be selected from a set list of folders) use a FolderPicker FileDialog Box. This code will return the Folder Path as a String. You can use it to save or export by concatenating the folder and the file name:

 GetAFolder & "\MyFileName.xls"

Function GetAFolder() as String
Dim dlg As FileDialog

Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
dlg.AllowMultiSelect = False
dlg.Show
GetAFolder = dlg.SelectedItems(1)
Set dlg = Nothing
End Function

You get flexibility and you use an interface people are familiar with.

 

[SeadnaS]

Where do i use this code?

 

[Gammachaser]

How are you exporting the table?

If using TransferSpreadsheet:

DoCmd.TransferSpreadsheet acExport,,[i]TableName[/i],GetAFolder & "\MyFileName.xls"

See the TransferSpreadsheet Method Help for details on setting the Range of cells, how to handle field names as headers, etc. for your particular needs.

If you are opening the spreadsheet, writing data to it and then doing a Save or SaveAs, use

GetAFolder & "\MyFileName.xls"

where you would put the filename in the Save (or SaveAs) Method.

 

[SeadnaS]

Heres my sub, where do i put in the code? This works with a button and a text box at the moment. You have to type the full path in the text box but i want it to be a drop down menu where you can select the path.

Public Sub cmdExportToExcel_Click()

On Error GoTo ErrorHandler

Dim strDocName As String
Dim strWorksheet As String
Dim strWorksheetPath As String
Dim strFullName As String
Dim strFileExists As Boolean
Dim varTest As Variant

With Me
If IsNull(.txtExportPath) Then
MsgBox "Enter an output path for your Excel file"
Exit Sub
End If

strDocName = "qt1"
strWorksheet = "QA_Table_Export"
strWorksheetPath = .txtExportPath & "\"
strFullName = strWorksheetPath & strWorksheet & ".xlsx"


End With



blnFileExists = MyFileExists(strFullName)
If blnFileExists Then
varTest = MsgBox("Overwrite file?" & vbCrLf & strFullName _
& vbCrLf & vbCrLf & _
"Note: No opens existing file.", vbCritical + vbYesNo, "Filename exists")
If varTest = vbYes Then
Kill strFullName
Else
RunExcel (strFullName)
Exit Sub
End If
End If

Call OpenExcelAddWorkbook(strFullName, "QA_Table_Export", strDocName, False)

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox Err.Description
Resume ErrorHandlerExit

End Sub

 

[Gammachaser]

Get rid of the textbox txtExportPath (which was going to be a combobox) and replace

strWorksheetPath = .txtExportPath & "\"

with

strWorksheetPath = GetAFolder & "\"

Don't forget to put the Function GetAFolder into the module.

 

[SeadnaS]

Thanks ill try that when i get in tomorrow.

 

[SeadnaS]

Error: user defined type not defined.
Lines highlighted: Function GetAFolder() As String
Dim dlg As FileDialog

 

[Gammachaser]

What version of Access are you using?

FileDialog should be in the Access Library (also Excel, Word and Office Libraries) as long as you are past Office 97 (Level 8). If you are in Access97, try loading a Reference to something like Excel, Word or Office 10 or higher. If you are not on a network that has an Office product at level 10 or above, you may have a problem.

 

[SeadnaS]

Access 2003

 

[PHV]

What about this function ?

Function PickFolder(strStartDir As Variant) As String
Dim SA As Object, f As Object
Set SA = CreateObject("Shell.Application")
Set f = SA.BrowseForFolder(0, "Choose a folder", 16 + 32 + 64, strStartDir)
If (Not f Is Nothing) Then
  PickFolder = f.Items.Item.path
End If
Set f = Nothing
Set SA = Nothing
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gammachaser]

SeadnaS -- Sorry I missed your previous reply.

Did you add the code for the GetAFolder Function I put in the first reply? As I pointed out in my third reply Don't forget to put the Function GetAFolder into the module.
That code can go into the form's Class Module or into a Standard Module. It should work for you.

 

[MacroScope]

It seems like you're going the long way around Chicago to get to where you want to go.

First, you create a table with as many of the drive syntaxes as you want in one text field. Then base the RowSource on that table field. You'll have a list box with those options selectable. Create another field on the same table and put your folder names there.

Now you have two controls, drivepath and folder. I assume the file name will be user selectable. Create a text box for the user to create the name.

On the AfterUpdate event of the FileName control, run a simple macro. It could have as few as one line in it, TransferSpreadsheet. In the options section of that operation in the macro, you'll see "File Name". Set it as follows:

=[DrivePath] & [Folder] & [FileName] & ".xls"

It will concatenate the file name and direct the spreadsheet where ever the user sends it.

 

[SeadnaS]

Hey, i don't actually need to implement this any more. We have merged all of our tables into one and I don't think it would be a good idea to create an excel sheet out of our huge table!

 

[MacroScope]

Ummm...you could use a query to limit how much of the data in the table you want to export.

 

[SeadnaS]

Wow can't believe i hadn't thought of that. I'm such an access noob!