473,320 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

docmd.transferspreadsheet freezes Excel

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.

Expand|Select|Wrap|Line Numbers
  1. strReps = "SELECT Software.[Software Name], Software.Version, Software.[Operating System], " & _
  2.             "Software.Status, Software.[Status Date], Software.[Approved Platforms], " & _
  3.             "Software.[Code Type], CUsage.[Cost Center], CUsage.[Entered On] " & _
  4.             "FROM Software INNER JOIN CUsage " & _
  5.             "ON Software.[Software Name] = CUsage.[Software Name] " & _
  6.             "AND Software.Version = CUsage.Version " & _
  7.             "AND Software.[Operating System] = CUsage.[Operating System] " & _
  8.             "WHERE Software.[Software Name] = '" & strSoftwareNameOI & "' " & _
  9.             "AND Software.Version = '" & strVersionOI & "' " & _
  10.             "AND Software.[Operating System] = '" & strOSOI & "' "
  11.  
  12. Set db = CurrentDb()
  13.         Set qry = db.CreateQueryDef("strSoftwareNameOI", strReps)
  14.  
  15.  
  16.         If DCount("*", "strSoftwareNameOI") > 0 Then
  17.         'MsgBox "NOT Empty"
  18.  
  19.  
  20.         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "strSoftwareNameOI", "C:\Documents and Settings\jmayhew\Desktop\testing.xls", True
  21.         db.QueryDefs.Delete ("strSoftwareNameOI")
  22.  
  23.        Else
  24.         'MsgBox "Empty"
  25.           db.QueryDefs("strSoftwareNameOI").SQL = "SELECT Software.[Software Name], Software.Version, Software.[Operating System], " & _
  26.             "Software.Status, Software.[Status Date], Software.[Approved Platforms], " & _
  27.             "Software.[Code Type], Space(30) = '0' AS [Cost Center], Space(30) = '0' AS [Entered On] " & _
  28.             "FROM Software " & _
  29.             "WHERE Software.[Software Name] = '" & strSoftwareNameOI & "' " & _
  30.             "AND Software.Version = '" & strVersionOI & "' " & _
  31.            "AND Software.[Operating System] = '" & strOSOI & "' "
  32.  
  33.  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "strSoftwareNameOI", "C:\Documents and Settings\jmayhew\Desktop\testing.xls", True
  34.         db.QueryDefs.Delete ("strSoftwareNameOI")
  35.  
  36. End If
  37.  
Jun 15 '10 #1
4 3549
jimatqsi
1,271 Expert 1GB
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
Jun 16 '10 #2
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
Jun 16 '10 #3
MMcCarthy
14,534 Expert Mod 8TB
Try adding in a DoEvents command between transferring the spreadsheet and deleting the query.

Expand|Select|Wrap|Line Numbers
  1.         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "strSoftwareNameOI", "C:\Documents and Settings\jmayhew\Desktop\testing.xls", True
  2.         DoEvents
  3.         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
Jun 16 '10 #4
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.
Jun 24 '10 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

6
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...
2
by: mcnewsxp | last post by:
how can i specify a particular worksheet using the docmd transferspreadsheet? thanks, mcnewsxp
3
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...
13
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...
4
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...
3
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...
1
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...
1
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. ...
2
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", _ ...
5
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...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
1
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)...
0
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...
1
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....
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.