Variable in TransferSpreadsheet method

[Guest]

Hi there,

I'm trying to do the following,

Select Case Forms!frmLookup.optInvAmt.Value
Case 1
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryFindGH", "f:\Documents and Settings\John\Desktop\exported.xls", True

There will be several other select statements so what I want to do instead
is have
my select statments change the value of a variable and have my
transferspreadsheet code run just once at the end. I did try this, but I get
an error message saying the object couldn't be found.

Can TransferSpreadsheet work with a variable or is there another way I can
do this?

Thanks

 

[Nikos Yannacopoulos]

Johnny,

Yes, it certainly can. There must be some other problem with your code,
so you better post the lot so people can look at it and (hopefully) see
what's wrong.

Nikos

 

[Guest]

K, thannks, here's the code I tried.
Private Sub Command41_Click()
Dim Path As String
Dim strQuery As String

Select Case Forms!frmLookup.optInvAmt.Value
Case 1
ExpData = "qrySelectedTax3"
Case 2
ExpData = "qrySelectedTax3Less"
Case 3
ExpData = "qrySelectedTax3Combo"
End Select
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"ExpData", "f:\Documents and
Settings\John\Desktop\exported.xls", _
True

If MsgBox("Do you want to see the exported data?", vbYesNo + vbQuestion,
"View Now?") = vbYes Then
Path = "f:\Documents and Settings\John\Desktop\exported.xls"
FollowHyperlink Path
End If

 

[Nikos Yannacopoulos]

Johnny,

The problem is that while you are using a variable ExpData to assign the
selected query name to, when the time comes to use it in your
TransferSpreadsheet, you enclose it in quotes, so instead of passing the
parameter value you are passing the parameter name (ExpData) as a
string! Just remove the quotes around it and it's fixed.

By the way, you are not explicitly declaring the variable; though VBA
does accept implicit declaring, like you are doing here (unless
instructed otherwise), it is good practice to declare your variables
explicitly so (a) it is easier for anyone reviewing the code to know
what they are supposed to be, and (b) it won't allow you to assign the
wrong data type if there is an error in the code, thus making debugging
easier (as opposed to implicit declaration, where the data type of the
first value assigned becomes the variable type).

HTH,
Nikos

 

[Guest]

Hi, thanks Nikos. I removed the quote and it works just fine now, thanks. I
did declare the variable, I just forgot to put in the code sample I posted.

Thanks again!

J