DoCmd.TransferSpreadsheet

[Guest]

hi,

my code below:

Private Sub Exporting_Click()
DoCmd.TransferSpreadsheet , acExport, acSpreadsheetTypeExcel9, "Screening
Log Query For Forms (Revised)", "C:\Screening_Log.xls", True
End Sub

is resulting a "Run Time Error: 2498" which explains an expression i entered
is the wrong data type for one of the arguments.

i are clueless.

 

[Ken Snell [MVP]]

You have an extraneous comma to the left of the acExport argument. Try this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Screening
Log Query For Forms (Revised)", "C:\Screening_Log.xls", True

 

[Guest]

Funny that it went ahead and compiled just fine until it tried to execute
though.

I really appreciate the 20-20 vision thing

Ted

 

[Guest]

.......ken:

forgot to add the following question: how would you restrict which fields
are actually included in the exported spreadsheet? or can you?

ted

 

[Ken Snell [MVP]]

Use a query that contains just the fields that you want to export. So, if
you just want two of the fields that are in the "Screening Log Query For
Forms (Revised)" query, create a new query that uses this query as the
source table, and just put the two fields on the grid. Save the query and do
the export with it.

 

[Guest]

gotcha, ken.

i thought of that before but figured there could be some
argument/specification available to the developer in the command under
discussion.

thanks 'gain.

ted

 

[Guest]

ken, this is on a slightly different tack...

my code is now:

Private Sub Exporting_Click()
Dim strName As String
strName = LAS_GetUserName() 'this function gets a user name, e.g. jdoe,
msmith, etc.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Screening Log
Query For Forms (Revised)", "C:\Documents and
Settings\strName\Desktop\Screening_Log.xls", True
If MsgBox("Do you want to view the file?", vbYesNo) = vbYes Then
FollowHyperlink "C:\Documents and
Settings\strName\Desktop\Screening_Log.xls"
End If

and i'm getting a Run Time Error: 3044 about the path not being valid.
ultimately, the mdb file will be sitting on a shared intra-net drive ("I")
which qualified users will be able to point to from their local desktops. i
would like them to be able to (now) save the spreadsheet onto their desktops
and so i'm trying to solve for a way to express the valid path in a general
way.

can you find the fly in the ointment.

 

[Ken Snell [MVP]]

Don't include the variable name in the text string. Instead, concatenate the
value of the variable into the string:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Screening Log
Query For Forms (Revised)", "C:\Documents and
Settings\" & strName & "\Desktop\Screening_Log.xls", True
If MsgBox("Do you want to view the file?", vbYesNo) = vbYes Then
FollowHyperlink "C:\Documents and
Settings\" & strName & "\Desktop\Screening_Log.xls"

 

[Guest]

aces, ken!

's fantastic...works like a charm

do i = 1 to 1000:

print "thanks ken"

end;