saving a file using file dialog in Access 2007

[Brian]

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,

 

[Klatuu]

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

 

[Brian]

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.

 

[Klatuu]

Happy to help out.
If you have any difficulties getting it to work, post back and we can get it
working.

 

[Brian]

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?

 

[Klatuu]

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.

 

[Brian]

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.

 

[Klatuu]

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.

 

[Brian]

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?

 

[Klatuu]

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

 

[Brian]

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.

 

[Klatuu]

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.