File Dialog Box

[graviz]

I am having some issues with updating some of my code. I currently have code to open a file dialog box, select and excel file, run it through some queries, and out put it to a selected location. How can I make a diaglog box pop up asking where I would like the file saved. The first set of code is the one that works without being able to select the output location. The second code is my attempt at the new code. What am I doing wrong?

Public Sub Import_Click()
Dim checkit As String
checkit = MsgBox("Did you save the E*Connect file as an Excel file?", vbYesNo, "Warning")
If checkit = vbNo Then
MsgBox "Please save the E*Connect file as an Excel file", vbCritical, "Error"
Else
On Error GoTo Err_openfolder_Click
Dim fd As FileDialog
Dim target_folder As String
Dim current_date As String
Dim file_name As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.ButtonName = "Import"
fd.Title = "Select a file to import"
fd.InitialView = msoFileDialogViewList
fd.AllowMultiSelect = False
fd.Filters.Clear
fd.Filters.Add "Microsoft Excel", "*.xls"
If fd.Show = -1 Then
DoCmd.SetWarnings 0
DoCmd.OpenQuery "Clear_Raw_Data"
'DoCmd.SetWarnings 1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Raw_Data", fd.SelectedItems.Item(1), 1
'MsgBox "Your File Has Been Imported"
DoCmd.OpenQuery "Add_Geomap_to_Raw"
DoCmd.OpenQuery "Clear_Master_Table"
DoCmd.OpenQuery "15_Minute_LEP_Work_Orders"
DoCmd.OpenQuery "15_Minute_LEP_CH_Work_Orders"
DoCmd.OpenQuery "15_Minute_Work_Orders"
DoCmd.OpenQuery "30_Minute_LEP_Work_Orders"
DoCmd.OpenQuery "30_Minute_LEP_CH_Work_Orders"
DoCmd.OpenQuery "30_Minute_Work_Orders"
DoCmd.OpenQuery "All_Work_Orders"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Sort_Work_Orders", "\\Mer2-corpfs1\dnsc\Resource Management\Dispatching Tool\Final_Route.xls", 1
DoCmd.SetWarnings 1
MsgBox "Your file has been saved to \\Mer2-corpfs1\dnsc\Resource Management\Dispatching Tool", vbOKOnly, "Process Complete"
DoCmd.Quit

End If
Set fd = Nothing
Exit_openfolder_Click:
Exit Sub
Err_openfolder_Click:
MsgBox "No Path Selected" & Err.Number & Err.Description
Resume Exit_openfolder_Click
End If
End Sub

-----------------------------------------------------------------

Public Sub Import_Click()
Dim checkit As String
checkit = MsgBox("Did you save the E*Connect file as an Excel file?", vbYesNo, "Warning")
If checkit = vbNo Then
MsgBox "Please save the E*Connect file as an Excel file", vbCritical, "Error"
Else
On Error GoTo Err_openfolder_Click
Dim fd As FileDialog
Dim target_folder As String
Dim current_date As String
Dim file_name As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.ButtonName = "Import"
fd.Title = "Select a file to import"
fd.InitialView = msoFileDialogViewList
fd.AllowMultiSelect = False
fd.Filters.Clear
fd.Filters.Add "Microsoft Excel", "*.xls"
Dim outputfd As FileDialog
Set outputfd = Application.FileDialog(msoFileDialogSaveAs)
outputfd.ButtonName = "Save As"
outputfd.Title = "Select a location to save the file"
outputfd.InitialView = msoFileDialogViewList
outputfd.AllowMultiSelect = False
outputfd.Filters.Clear
outputfd.Filters.Add "Microsoft Excel", "*.xls"
If fd.Show = -1 Then
DoCmd.SetWarnings 0
DoCmd.OpenQuery "Clear_Raw_Data"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Raw_Data", fd.SelectedItems.Item(1), 1
DoCmd.OpenQuery "Add_Geomap_to_Raw"
DoCmd.OpenQuery "Clear_Master_Table"
DoCmd.OpenQuery "15_Minute_LEP_Work_Orders"
DoCmd.OpenQuery "15_Minute_LEP_CH_Work_Orders"
DoCmd.OpenQuery "15_Minute_Work_Orders"
DoCmd.OpenQuery "30_Minute_LEP_Work_Orders"
DoCmd.OpenQuery "30_Minute_LEP_CH_Work_Orders"
DoCmd.OpenQuery "30_Minute_Work_Orders"
DoCmd.OpenQuery "All_Work_Orders"
outputfd.Show
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Sort_Work_Orders", outputfd.SelectedItems.Item(1), 1
DoCmd.SetWarnings 1
MsgBox "Your file has been saved" & outputfd, vbOKOnly, "Process Complete"
DoCmd.Quit
End If
Set fd = Nothing
Set outputfd = Nothing
Exit_openfolder_Click:
Exit Sub
Err_openfolder_Click:
MsgBox "No Path Selected" & Err.Number & Err.Description
Resume Exit_openfolder_Click
End If
End Sub

 

[Alisa]

check this example: http://www.mvps.org/access/api/api0001.htm

 

[graviz]

No luck on that one. Any idea how to modify my current code to get it to work?

 

[Alisa]

What problem did you run into? I have that code in a module and I use it all over the place - it is pretty flawless. Are you having trouble figuring out how to call it or?

 

[Alisa]

It's not that I don't want to help you with your own code, but I just don't understand exactly what problem you are having.

 

[graviz]

"438 Object doesn't support this property or method" It's possible I'm not using the code correctly. How would you use the code in my example.

 

[Alisa]

Which line of code generates the error?

 

[graviz]

strSaveFileName = ahtCommonFileOpenSave

 

[Alisa]

hmmm I don't see that line in the code you posted. Can you repost your code again, using code tags this time, and then highlight the line with the problem in red? In general that is the best way to get help with code on this forum.

 

[graviz]

I posed my code originally and did not recieve help as to what I was doing wrong. I spent most of last night trying to get the code you posted yesterday to work and I was unable to figure it out. I decided to try and work on the code I already had since it all worked with the exception of the last part, so I removed the other code so I don't have how I plugged it in anymore. The only reason I knew which line caused the error was it kept going back to that line no matter what I did so I remember that very well. I apologize if I’m not asking for help in the right way, but after spending hours trying to use that code without any success it can get frustrating. If you are not sure how to use your code in my project I understand, but thanks for trying to help.

 

[Alisa]

I know it can be frustrating when you don't get the answer you are looking for. There is a great sticky on the board that explains techniques for getting help here - you might want to read it. From my perspective, I gave you a reference to a piece of code that does exactly what you said you were looking for. You said you couldn't get it to work, but didn't explain where you were stuck. Then you said you were getting an error message in your original code, but are unable to show me where exactly the error is occurring. I am trying my best to be helpful, but I can only answer the questions you ask, I can't read your mind. Again, I and lots of other people on this forum are more than willing to help you, but you have to learn how to ask the question.

 

[graviz]

I'll give it another shot tonight and let you know what I find. In the meantime, does anyone know how to read my code and who might be a little bit more familiar with the "File Dialog" function so I don't have to reinvent the wheel? Thanks!