 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / May 2008saving a file using file dialog in Access 2007
I am trying to use FileDialog in combination with TransferSpreadsheet to save a file named by the user in a directory of the user's choice. I am able to bring up the SaveAs dialog box with no problem but the user has to select a file in order to save the current file as that file. In my program, the user pulls data specific to a month and year and a table is created that has in its name the month and year that has just been entered. Therefore, I would like to avoid having the user retype in the information and just pass the filename string to save the file using the dialog box. I have tried using the api0001.htm but quite frankly cannot get it to work. Regardless, I can get the dialog box to work in either the open file or save file mode but I need to know if there is a way to save a file with a string using FileDialog. I am not a sophisticated programmer, in fact even using "programmer" is a stretch. :-) Any help will be much appreciated. Thanks,  SignatureBrian If the api call you referenced is not working for you, then you are not using it correctly. I have been using it for years with no problems. It has been so long since I used the file dialog, I don't remember if it is possible using that object, but I do know that with the api, you can pass a default file name and that will show as the file name to save. Here is an example where I do this: 'Set up default path and file strCurrYear = Me.txtCurrYear strCurrMonth = Me.cboPeriod.Column(1) 'Set up default path and file strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" & strCurrYear _ & " Actuals\" & strCurrMonth & "\FFP Charts\" strDefaultFileName = Me.cboOffering & " Summary " & Me.cboPeriod.Column(1) _ & " " & Me.txtCurrYear & ".xls" 'Set filter to show only Excel spreadsheets strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)") 'Flags Hides the Read Only Check and Only allow existing files lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_OVERWRITEPROMPT 'Call the Open File Dialog varGetFileName = ahtCommonFileOpenSave( _ OpenFile:=False, _ InitialDir:=strDefaultDir, _ Filter:=strFilter, _ Filename:=strDefaultFileName, _ Flags:=lngFlags, _ DialogTitle:="Save Report") If varGetFileName <> "" Then xlBook.SaveAs Filename:=varGetFileName End If SignatureDave Hargis, Microsoft Access MVP > I am trying to use FileDialog in combination with TransferSpreadsheet to save > a file named by the user in a directory of the user's choice. I am able to [quoted text clipped - 12 lines] > I am not a sophisticated programmer, in fact even using "programmer" is a > stretch. :-) Any help will be much appreciated. Thanks, Well yes you are correct I was not using it correctly but first I was trying to use the code at the top of the referenced page for the Save file. I actually got it to work using your code that you provided to some else in a thread I found and now think I can adapt it to my needs. Thanks for responding to me and others, it really helps. SignatureBrian > If the api call you referenced is not working for you, then you are not using > it correctly. I have been using it for years with no problems. It has been [quoted text clipped - 46 lines] > > I am not a sophisticated programmer, in fact even using "programmer" is a > > stretch. :-) Any help will be much appreciated. Thanks, Happy to help out. If you have any difficulties getting it to work, post back and we can get it working. SignatureDave Hargis, Microsoft Access MVP > Well yes you are correct I was not using it correctly but first I was trying > to use the code at the top of the referenced page for the Save file. I [quoted text clipped - 52 lines] > > > I am not a sophisticated programmer, in fact even using "programmer" is a > > > stretch. :-) Any help will be much appreciated. Thanks, Thanks. As a matter of fact, I am 99% there but I get an 'object required error" here: If varGetFileName <> "" Then xlBook.SaveAs Filename:=varGetFileName End If I checked my spelling and everything looks good. Does itmatter that I am using Access 2007? SignatureBrian > Happy to help out. > If you have any difficulties getting it to work, post back and we can get it [quoted text clipped - 56 lines] > > > > I am not a sophisticated programmer, in fact even using "programmer" is a > > > > stretch. :-) Any help will be much appreciated. Thanks, In my code, xlbook is the object reference to the workbook name. You may need to change that and any other names to use your names. I haven't heard 2007 is a problem, but I haven't used it yet in 2007, so I can't say for sure. You could tell if it is returning the value you expect by putting a break point on this line of code: If varGetFileName <> "" Then And looking at the value of the variable varGetFileName If it has the full path and file name you selected, then it is working okay. SignatureDave Hargis, Microsoft Access MVP > Thanks. As a matter of fact, I am 99% there but I get an 'object required > error" here: [quoted text clipped - 66 lines] > > > > > I am not a sophisticated programmer, in fact even using "programmer" is a > > > > > stretch. :-) Any help will be much appreciated. Thanks, I tried the break point in the code and I am getting the path and filename. So it is working for the most part except for the whole object thing. Unfortunately, my lack of programming knowledge is showing. I am stuck on the following: Dim xlBook as Object Set xlBook = ? ' I need a valid object reference I have a feeling this is pretty basic. SignatureBrian > In my code, xlbook is the object reference to the workbook name. You may > need to change that and any other names to use your names. [quoted text clipped - 79 lines] > > > > > > I am not a sophisticated programmer, in fact even using "programmer" is a > > > > > > stretch. :-) Any help will be much appreciated. Thanks, Brain, I think I may have gotten you lost from your original intent. Rereading your original post, it appears you are wanting to use the file name and path returned from the api as the location to save a spreadsheet with the TransferSpreadsheet method. If this is what you are after, then the xlbook part is incorrect. What you need to do is use the returned value in the TransferSpreadsheet method. You would put the varialbe varGetFileName in the FileName argument of the TransferSpreadsheet: Docmd.TransferSpreadsheet acExport, , "TableName, varGetFileName, True If that is not what you're after, let me know. SignatureDave Hargis, Microsoft Access MVP > I tried the break point in the code and I am getting the path and filename. > So it is working for the most part except for the whole object thing. [quoted text clipped - 89 lines] > > > > > > > I am not a sophisticated programmer, in fact even using "programmer" is a > > > > > > > stretch. :-) Any help will be much appreciated. Thanks, Klatuu, That did work. Thanks. If you did get me unto a tangent a bit it was not all for naught - it was a learning experience; learning what I do not know. :-) BTW, before your last post I tried this: Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add xlBook.SaveAs FileName:=varGetFileName xlApp.Quit Set xlBook = Nothing Set xlApp = Nothing Everything seemed to work but in actuality an Excel file was not created because when I tried to open the file I got an error saying the file was not in the same format as the extension. When I opened it anyway, it was blank! What did I do wrong? SignatureBrian > Brain, > [quoted text clipped - 103 lines] > > > > > > > > I am not a sophisticated programmer, in fact even using "programmer" is a > > > > > > > > stretch. :-) Any help will be much appreciated. Thanks, You created a workbook, but never put anything in it. 'Starts an instance of Excel running: Set xlApp = CreateObject("Excel.Application") 'Creates a workbook object: Set xlBook = xlApp.Workbooks.Add Did not do anything before saving the empty object: xlBook.SaveAs FileName:=varGetFileName SignatureDave Hargis, Microsoft Access MVP > Klatuu, > [quoted text clipped - 124 lines] > > > > > > > > > I am not a sophisticated programmer, in fact even using "programmer" is a > > > > > > > > > stretch. :-) Any help will be much appreciated. Thanks, Klatuu, That makes sense. However, asking how to populate the workbook is another lesson that I will not ask of you at this time. I will try some reading on my own in the mean time. Thanks for all your help and patience in solving my problem. SignatureBrian > You created a workbook, but never put anything in it. > [quoted text clipped - 135 lines] > > > > > > > > > > I am not a sophisticated programmer, in fact even using "programmer" is a > > > > > > > > > > stretch. :-) Any help will be much appreciated. Thanks, When you do get to that point, it is much like object referencing in Access. You start with the Application, a workbook, then a worksheet within the workbook, then you have cells, and borders, and so on. The Excel model has a lot more properties than does Access, so you will be writting a lot of code. One thing to keep uppermost in mind when doing this is that when using an Excel object model in Access, it is very important to be sure all your objects are carefully and fully qualified. What will happen if you don't is that you will find an instance of Excel running in the Task Manager under the Processes tab. That happens when an Excel object is referenced that is not fully qualified. Access doesn't know what it belongs to, so it will create an additional instance of Excel you don't know about. So when you Quit the instance you created, the one Access created is still running. This is the most common problem when using Acces to Excel autmation. SignatureDave Hargis, Microsoft Access MVP > Klatuu, > [quoted text clipped - 142 lines] > > > > > > > > > > > I am not a sophisticated programmer, in fact even using "programmer" is a > > > > > > > > > > > stretch. :-) Any help will be much appreciated. Thanks,
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.