transfer spreadsheet

[Guest]

Hi,
I am trying to use the Ken Getz api code for load/ save diaolog box in
conjunction with a simple runsql.transferspreadsheet command. the runsql
works fine if i put in a normal path , and the getz code works by itself in
terms of selecting a file.

But: how do I put them together? I mean, how do I get the value that is
returned from the api code (ie the filename) to inform the sql. I have tried
various things but to no avail, and though this experience is leading me to
think I should give up on vba altogether, I do wish to get this one working
first.

At the mo, I have the runql sitting alone in a standard module. The main api
code is also sitting in a standard mod, by itself.

Any help for disheartened a noob would be greatly appreciated. Thanks

 

[Guest]

Have you tried a public variable? E.g. Public myFile As String at the top of
one of the modules. You can then set myFile equal to the filename and/or
path returned by the dialog box before substituting myFile for the
filename/path in your transferspreadsheet code.

 

[Guest]

Thanks Martin,


I have followed your suggestion, which makes perfect sense, but have come up
against a problem I have encounterd previously - which is that the
transferspreadsheet command does not seem to accept Myfile (or anything other
than an actual filepath) in the part of the argument where it expects a
filename. I get an "arguamnt not optional" compile error. Any ideas?

Thanks again

 

[Guest]

That's not the way it works. You are mixing two differnt things. If you use
the API you mentioned, all it does is retrieve a path and filename you can
then use however you want. If you are using the RunSql, then you are
creating an SQL string that will be used by the RunSql.

If you want to use the TransferSpreadsheet, you will need to use either a
table or a saved query as the source. Then in the TransferSpreadsheet, use
the variable returned from the Open/Save dialog as the file name argument and
the name of your query or table as the tablename argument.

Now, if it is possible that what you transfer depends on user import and/or
program logic, you will need to do one of two things, depending on the
complexity of the query requirements. If all that is required are parameter
values, you can build them into your stored query. If it is necessary to
construct the SQL statment each time you run the code because different
fields or orders, etc are required, then you will need to go to VBA Help and
read up on the CreateQueryDef Method.

 

[Guest]

Klatuu,

Thanks. To get this working, I am ignoring the sql side of things in order
to get the load/ save dialog working with transferspreadsheet, (using an
existing table). When I do as I think you are suggesting, ie:

Function Export()

DoCmd.TransferSpreadsheet acExport, , "tblEmployees",
modCommonDialog.TestIt.strfilter, True, ""

End Function

....where TestIt is the procedure within the api code used to call the
filename variable I get the error: "object not defined" suggesting that it is
not satisfied with what Testit is returning.

Thanks for your patience

 

[Guest]

Sorry: that error message should have read "Object Requred" (see above)

Thanks

 

[Guest]

I don't think you should have anything after the "True" in your expression
(exports don't use the Range argument).

 

[Guest]

The myth that TransferSpreadsheet does not support a range argument is false.
I do it all the time. Why Microsoft says it doesn't work is beyond me. I
can only attribute it to the fact that their documentation and Help does not
stay current with the product. For example, in 2003 Help, it makes almost no
mention of the fact that OpenArgs can be used with reports.

 

[Guest]

You need to change your approach a little:
First, you don't need to reference a module to call a function from it.
Rather what you want to do is use a variable to get the value and pass it to
the method. Also, the TestIt function in that code will not return anything,
What you want is the GetOpenFile function:

strFileName = GetOpenFile()

DoCmd.TransferSpreadsheet acExport, , "tblEmployees", strFileName, True

It is not necessary to include anything past the last argument you want to
pass to the method, so the ,"" after True is not necessary and in this case,
may cause a problem because it will create a worksheet named "" or create an
error.

 

[Guest]

Klatuu, that last post did the trick perfectly. Thank you greatly for your
help!