My problem is that Access freezes up whenever I try to use the docmd.transferspreadsheet method, I can use the docmd.outputto and it works fine but I need to be able to use the transferspreadsheet method so I can have multiple queries in one excel file.
If anyone can help as what the problem may be, I would greatly appreciate it. -
strReps = "SELECT Software.[Software Name], Software.Version, Software.[Operating System], " & _
-
"Software.Status, Software.[Status Date], Software.[Approved Platforms], " & _
-
"Software.[Code Type], CUsage.[Cost Center], CUsage.[Entered On] " & _
-
"FROM Software INNER JOIN CUsage " & _
-
"ON Software.[Software Name] = CUsage.[Software Name] " & _
-
"AND Software.Version = CUsage.Version " & _
-
"AND Software.[Operating System] = CUsage.[Operating System] " & _
-
"WHERE Software.[Software Name] = '" & strSoftwareNameOI & "' " & _
-
"AND Software.Version = '" & strVersionOI & "' " & _
-
"AND Software.[Operating System] = '" & strOSOI & "' "
-
-
Set db = CurrentDb()
-
Set qry = db.CreateQueryDef("strSoftwareNameOI", strReps)
-
-
-
If DCount("*", "strSoftwareNameOI") > 0 Then
-
'MsgBox "NOT Empty"
-
-
-
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "strSoftwareNameOI", "C:\Documents and Settings\jmayhew\Desktop\testing.xls", True
-
db.QueryDefs.Delete ("strSoftwareNameOI")
-
-
Else
-
'MsgBox "Empty"
-
db.QueryDefs("strSoftwareNameOI").SQL = "SELECT Software.[Software Name], Software.Version, Software.[Operating System], " & _
-
"Software.Status, Software.[Status Date], Software.[Approved Platforms], " & _
-
"Software.[Code Type], Space(30) = '0' AS [Cost Center], Space(30) = '0' AS [Entered On] " & _
-
"FROM Software " & _
-
"WHERE Software.[Software Name] = '" & strSoftwareNameOI & "' " & _
-
"AND Software.Version = '" & strVersionOI & "' " & _
-
"AND Software.[Operating System] = '" & strOSOI & "' "
-
-
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "strSoftwareNameOI", "C:\Documents and Settings\jmayhew\Desktop\testing.xls", True
-
db.QueryDefs.Delete ("strSoftwareNameOI")
-
-
End If
-
4 3549
Let's see, I've seen this, a year or more ago, I'm not sure I can tell right off the bat ... either quit Excel before you do the transfer, or make sure Excel is open, I think it's one of those two.
Or, possibly there is a window waiting for a response from you and it's hidden behind another window. Try hitting Alt-Tab to see what other windows are open. You might find a message box waiting for your input.
I'm sorry, it's too late for me to research it now, or I'd give you a more definitive answer.
Jim
Jim,
Thanks for the response, I have tried both closing excel and having it open and neither works, Access still freezes and I get the program is not responding and Access closes automatically. I'm lost as to what is going on.
If you have any other ideas as to what the problem may be or any examples or anything, please let me know.
Thank you
Try adding in a DoEvents command between transferring the spreadsheet and deleting the query. - DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "strSoftwareNameOI", "C:\Documents and Settings\jmayhew\Desktop\testing.xls", True
-
DoEvents
-
db.QueryDefs.Delete ("strSoftwareNameOI")
I suspect VBA tries to delete the query before it finishes transferring the spreadsheet. The DoEvents command should take care of that.
Mary
Thanks for the responses, I tried both suggestions and neither worked.
I did figure out what is causing the problem though but I am unsure of how to fix it.
The problem is coming from the ADOBD.Recordsets, and this is causing it to crash.
I ran a test sample using DAO.Recordset and it worked but for this program it has to be ADOBD.Recordsets because they reference other things in the program.
Anybody have any suggestions as to why the ADOBD.Recordsets is causing this?
Thanks.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jack Smith |
last post by:
Help needed on this question. Any help is appreciated. Thanks in
advance.
Given a binary string (i.e. a finite sequence of 0's and 1's) we
choose any two digit substring 01 and replace it by a...
|
by: mcnewsxp |
last post by:
how can i specify a particular worksheet using the docmd
transferspreadsheet?
thanks,
mcnewsxp
|
by: user_5701 |
last post by:
Hello, I'm getting an error with a Docmd.Transferspreadsheet line of
code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000,
"tblTest", pathAndFilename, True
The above line...
|
by: aleksandra_83 |
last post by:
Hello,
I have searched all over google groups and internet, but I have not
found a solution to this problem that has worked. The solutions I found
helped me single out the line that is causing...
|
by: Tom Louchbaum |
last post by:
I try to use this command to import a spreadsheet
"DoCmd.TransferSpreadsheet acImport, 8, tablename & " -tbl", DIR &
"Book2.xls", True"
and it errors out. If I try to import that same...
|
by: sranilp |
last post by:
Hey All,
Actually I need to export the data from Access to Excel particular spreadsheet(ie.Raw Data),so I was using Docmd.Transferspreadsheet but in this syntax where i can give the spreadsheet...
|
by: Joel Fireman |
last post by:
Help Needed: Upgrade Fedora 4 / Apache 2 to PHP 5.2.x from 5.0.4
I've been testing Joomla as a content manager for the County offices,
and it looks pretty good. Unfortunately, I decided to...
|
by: troy_lee |
last post by:
Here is my code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "C:
\Databases\RMA Database Redesign\ParetoTemplate2.xls"
Access says that I need to assign a file path or name.
...
|
by: amir369 |
last post by:
Hi All,
I'm trying to import an Excel sheet, but with more the one rang, couse the columns aint sequence.
for example:
DoCmd.TransferSpreadsheet acImport, , "DailyRprzntvRpt", _
...
|
by: jerelp |
last post by:
I have a form with one button the code for that button is
On Error Resume Next
DoCmd.TransferSpreadsheet acImport, 8, "FuelConsumption NewData", Me.tb_FileName, 1, "sheet2$"
On Error Resume...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |