Access and Excel NOT playing Nice

[gakiss2]

I want to send some data from a query to a macro enabled (.xlsm) Excel workbook and then open it (the excel workbook) from access. I have had to do some work arounds that don't make sense just to get as far as I have gotten so I would be happy to hear the 'right' way to do it so I don't need all these gymnastics AND I ultimately can't get it to work. I guess this is technically an Access problem but it does involve Excel (at least to the extent that I can't seem to get done what I need from Access). I hope it is OK to bring Excel in to the conversation as well, at least it is all VBA.

I first tried to export the data from Access to the Excel but I had lots of issues and finally got limited success by exporting to a non macro enabled (.xlsx) workbook. Which helps because if I do that then I can then just open the macro enabled excel from access and then transfer the data using the "on open" procedure in the macro enabled excel sheet. I know I'm getting dizzy just describing it but that is what has gotten me as far as I am. And why I thought it would be best to get help here because I don't know why I can't push some query data to a macro enabled workbook. If I (we) can do that then I don't have to do anything (complicated) in the Excel. Its just a smattering of data so its super easy to then get the data to the sheet and cells in Excel that are needed. Here is the data:

Title	PartNum	AssignedVendor	SQEName	NewLBTrackNo
title	ghir	AKG	Gary Kissick	DEV-GK-20-130

The Excel error message is 'out of range' which I can't figure but not sure that is relevant to the Access discussion. But, again, it seems if this is done correctly from Access then Excel doesn't need to be a part of the discussion.

and the code I have now in Access:

Private Sub cmdSendDev_Click()

Dim wb As Workbook



    '  Module 5 contains some code that could be converted to pull the DEV template
    ' out of an attachment in tblFormAttach
    
' "C:\Users\gkissick\Desktop\DeviationFormSBQD.xlsm"

'  Either open DEV template then import from this DB or export to DEV template then

'  the data to go to the Deviation is in qryDEV
DoCmd.OutputTo acOutputQuery, "qryDEV", acFormatXLSX, "C:\Users\gkissick\Desktop\DeviationFormData.xlsx"

' DoCmd.OutputTo acOutputQuery, "qryDEV", acFormatXLSB, "C:\Users\gkissick\Desktop\DeviationFormSBQD.xlsm"
Application.FollowHyperlink "C:\Users\gkissick\Desktop\DeviationFormData.xlsx"
Application.FollowHyperlink "C:\Users\gkissick\Desktop\DeviationFormSBQD.xlsm"

End Sub

I tried TransferSpreadsheet and had lots of issues. I wouldn't transfer into the enabled sheet even though I picked acExcel12Xml format (that's not the exact format syntax). I couldn't even file a format for enabled sheets with OutputTo. I even had trouble just opening the dang excels until I went to App.. Hyperlink. In some trys the file being outputted to got corrupted and couldn't open, the error messages hinted at the data format didn't match up with the extension of the file.

Here is the (gymnastics ridden) Excel Code:

Private Sub Workbook_Open()


Dim dev As Worksheet
Set dev = Sheet1
Dim eng As Worksheet
Set eng = Sheet6
' Workbooks.Open "C:\Users\gkissick\Desktop\DeviationFormData.xlsx"
MsgBox "Wait"

eng.Unprotect "LooseNuts"
Workbooks("C:\Users\gkissick\Desktop\DeviationFormData.xlsx").Worksheets("qryDEV").Range("A2:E2").Copy _
ThisWorkbook.Worksheets("Engine").Range("K1:O1")
' Workbooks("C:\Users\gkissick\Desktop\DeviationFormData.xlsx").Worksheets("qryDEV").Range("A29").Copy _
' Workbooks("C:\Users\gkissick\Desktop\DeviationFormSBQD.xlsm").Worksheets("Engine").Range("K1")
'
' Below Code works fine ' its not part of this issue


Sheets("Deviation Form").Range("A1").Select

Set sh = ThisWorkbook.Sheets("Deviation Form") '
sh.Unprotect "LooseNuts"
Sheets("Deviation Form").Range("B33") = Null
sh.Protect "LooseNuts", UserInterfaceOnly:=True

Sheets("Deviation Form").TglLock = True
Sheets("Deviation Form").TglLock.Visible = False
Application.Wait Now + TimeValue("00:00:03")

' Unload ufWelcom

End Sub

I tried to upload the Excel but the site won't let me. I really don't think there is anything other than the above that would matter. it had all worked with manual entry instead of getting that from Access. The point of this exercise is that these excel sheets are kicked off from the creation of a record in the Database and this process give a jump start by pushing the data that is shared between them into the excel sheet.

I have been grinding on this one for a few hours now and I am stuck. Thanks for your help.

 

[Gasman]

And on what line do you get the error?

 

[gakiss2]

And on what line do you get the error?

the error is here:
Workbooks("C:\Users\gkissick\Desktop\DeviationFormData.xlsx").Worksheets("qryDEV").Range("A2:E2").Copy _
ThisWorkbook.Worksheets("Engine").Range("K1:O1")

the previous line to unprotect the sheet is likely not needed. Its just something I tried in case that was it. the sheet is not protected, at least to my knowledge.

 

[gakiss2]

And on what line do you get the error?

Adding to my previous. Maybe useful: I believe the excel file has to be open for that line to work. The file that the data is coming from and the one the data is going to is (was) opened in the Access Code.
Application.FollowHyperlink "C:\Users\gkissick\Desktop\DeviationFormData.xlsx"
Application.FollowHyperlink "C:\Users\gkissick\Desktop\DeviationFormSBQD.xlsm"

 

[Gasman]

Are you running this code from Access or Excel?
If from Access, then I believe you need to qualify the objects with the Excel object variable.?

I certainly would be opening the files and then work between them, but then I am not an Excel expert either.?

 

[gakiss2]

the error is here:
Workbooks("C:\Users\gkissick\Desktop\DeviationFormData.xlsx").Worksheets("qryDEV").Range("A2:E2").Copy _
ThisWorkbook.Worksheets("Engine").Range("K1:O1")

the previous line to unprotect the sheet is likely not needed. Its just something I tried in case that was it. the sheet is not protected, at least to my knowledge.

Private Sub cmdSendDev_Click() is Access Code
and Private Sub Workbook_Open() is the Excel Code

I would like to move it all to Access and then launch the Excel after the data is exported to the Excel wkbook, if that is possible. I certainly seems feasible and there is no benefit to trying to import from Access into Excel. but if that is the only way then 'As long as it works its OK.... ' ??

 

[Minty]

As @Gasman has stated you need to open your workbook correctly.
Assuming you are exporting a query then something like this will work.

   Dim xlApp as Object
   Dim xlWB as Object
   Dim sFilename as String
  
   'Set the filename with path'
   sFileName = c:\myexcelfiles\examplefile.xlsm"
  
   'Stick some data into it
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryNameGoesHere", sFileName
  
  'open workbooks to format correctly   
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open(sFileName)
    xlApp.Visible = True

Note that the sheet name will always be the query name in this example. You can obviously change that afterwards in code if required.

 

[Gasman]

There is also a method using copying from a recordset.

https://docs.microsoft.com/en-us/office/vba/api/excel.range.copyfromrecordset

arnelgp demonstrates it here

Suffice to say it can be done, plus it has been asked several times on here. Also look at Similar Threads as the bottom of this thread.
We also have a good Search feature on here.

 

[gakiss2]

As @Gasman has stated you need to open your workbook correctly.
Assuming you are exporting a query then something like this will work.

   Dim xlApp as Object
   Dim xlWB as Object
   Dim sFilename as String
 
   'Set the filename with path'
   sFileName = c:\myexcelfiles\examplefile.xlsm"
 
   'Stick some data into it
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryNameGoesHere", sFileName
 
  'open workbooks to format correctly  
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open(sFileName)
    xlApp.Visible = True

Note that the sheet name will always be the query name in this example. You can obviously change that afterwards in code if required.

Thank You for the help. I remember getting this while I was originally trying to do it all in Access. Part of what drove me to try to do it in Excel.

Since I am referencing a qry instead of a table for the output I am not sure what it means. there are no multi-valued fields in the Query and I am not aware of an IN clause anywhere. The only thing that remotely resembles this is that the table the query is based on contains a long text field with append only set to yes, but that field is NOT in the query I am exporting. And referring to another database? I have no ideas...

Here is code after I put in my details.

Private Sub cmdSendDev_Click()

Dim xlApp As Object
   Dim xlWB As Object
   Dim sFilename As String
 
   'Set the filename with path'
   sFilename = "C:\Users\gkissick\Desktop\DeviationFormSBQD.xlsm"
 
   'Stick some data into it
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryDEV", sFilename
 
  'open workbooks to format correctly
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open(sFilename)
    xlApp.Visible = True

End Sub

 

[Minty]

Interesting. Can you post the SQL of your query, and any queries it might rely on?

 

[gakiss2]

OK Some progress (??). On the query that I am trying to export there is a criteria that is equal to a control on the form. Now the control is the 'Document Number' which is just a string variable, nothing multi-valued about it. But I deleted that Criteria and I worked. Of course I dumped 1200 records and I only want one of them but I can pick it out with Excel code. If anyone still wants to help, I would like to do this cleanly so it only exports the single line of data (Record) that I want from the query.

Here is the version that didn't work

What did work was to just delete the critera for NewLBTrackNo

 

[gakiss2]

Interesting. Can you post the SQL of your query, and any queries it might rely on?

Here is what Query View gives:
SELECT tblDocsIssued.Title, tblDocsIssued.PartNum, tblDocsIssued.AssignedVendor, tblDocsIssued.SQEName, tblDocsIssued.NewLBTrackNo
FROM tblDocsIssued
WHERE (((tblDocsIssued.NewLBTrackNo)=[Forms]![frmDocDetail]![NewLBTrackNo]));

 

[gakiss2]

Or can you directly export the result of a Query then just copy that 'query view' from above directly into the code??

 

[gakiss2]

Or is it possible to just pluck these off the open form, put them in a string with seperators then export the string to Excel?

 

[Minty]

I suspect its to do with the VBA export not understanding the form reference in the query.
You may need to do this with query parameters and a query definition. It's a little more work but cleaner.

I'm too busy atm to find a suitable example immediately but will post one back later.

 

[gakiss2]

Thank You for your help. I pulled the data straight off the open form and put it into a table. sort of recreating what the query was supposed to do but shouldn't have any trace of where they came from so it should solve the issue as a work around. so far so good until Access can't find the le despite the fact that it just micro seconds ago, put data into said table.

Here is latest. back to work arounds but still cleaner than ping ponging data back and forth in Excel

Private Sub cmdSendDev_Click()

Dim xlApp As Object
   Dim xlWB As Object
   Dim sFilename As String
  Dim sendstring As String
  Dim strSQL As String
    
  ' Prepare string to export
  sendstring = ""
  sendstring = Me.PartNum & " % " & Me.SQEName & " % " & Me.AssignedVendor & _
     " % " & Me.Title & " % " & Me.txtEmailStr
   Debug.Print "string " & sendstring
strSQL = "INSERT INTO tblExport (export) VALUES ('" & sendstring & "' );"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
 DoCmd.OpenTable "tblExport"
 
   'Set the filename with path'
   sFilename = "C:\Users\gkissick\Desktop\DeviationFormSBQD.xlsm"
 
   'Stick some data into it
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblExport", sFilename
 
  'open workbooks to format correctly
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open(sFilename)
    xlApp.Visible = True

End Sub

 

[Gasman]

I am thoroughly confused.

You say the criteria was a form control called [Document Number], yet that is not what is used in the query?

Did you look at arnelgp's usage in that link I posted.?

 

[Isaac]

These are just a few random tips on your current code.

• You don't need to set sendstring to "". A zero length string is the default "starting out" value for a string variable.
• If you SetWarnings off, don't forget to set them back on somewhere, including but not limited to an error handler. One could argue this, but it is good coding to return things to the state that they were if temporarily changing them.
• Use Currentdb.Execute (or better yet, the .Execute method called against a database variable already SET to CurrentDb), which will eliminate the need to suppress warnings at all

 

[gakiss2]

I am thoroughly confused.

You say the criteria was a form control called [Document Number], yet that is not what is used in the query?

Did you look at arnelgp's usage in that link I posted.?

Sorry I can be confusing sometimes,

Yes, I looked at arnelgp's usage. I have gotten some good advice there. The article was using OutputTo. I haven't had any success there. Only TransferSpreadsheet. I guess its because I'm passing the data to a macro enabled Excel book.

I used some shortcuts in describing things. the criteria in the Query comes from a control on a form. that control is tied to a field in a table that is called NewLBTrackNo. I shortened that to 'Document Number' in the above discussion. I can see how that is confusing. My confusion on the issue comes from the confusing error message. The control used in the Query (NewLBTrackNo) is not multivalued, not from another Database, and etc.

Since then I was trying to figure out what it could be so I took that criteria out of the Query. It DID get rid of the error message but, since that criteria is not there, the output that got exported to the Excel included all the records, not just the one record which used that specific NewLBTrackNo.

Now I am tryin this approach:
Build a string with the data that I wanted from the form. just me.control1 & " % " & me.control2 & etc. etc.
use InsertInto to put that in a table, also put NOW into the next column of the table - new table called tblExport
make a new query which pulls only the last entry - as opposed to deleting the record later - six of one , half a dozen fo the other
Then export THAT new query to the Excel. Should work but the latest travail is that access can't find the table despite the fact that
it is successfully inserting the above data into the new table.

Please let me know if you have any more questions.

 

[gakiss2]

Sorry I can be confusing sometimes,

Yes, I looked at arnelgp's usage. I have gotten some good advice there. The article was using OutputTo. I haven't had any success there. Only TransferSpreadsheet. I guess its because I'm passing the data to a macro enabled Excel book.

I used some shortcuts in describing things. the criteria in the Query comes from a control on a form. that control is tied to a field in a table that is called NewLBTrackNo. I shortened that to 'Document Number' in the above discussion. I can see how that is confusing. My confusion on the issue comes from the confusing error message. The control used in the Query (NewLBTrackNo) is not multivalued, not from another Database, and etc.

Since then I was trying to figure out what it could be so I took that criteria out of the Query. It DID get rid of the error message but, since that criteria is not there, the output that got exported to the Excel included all the records, not just the one record which used that specific NewLBTrackNo.

Now I am tryin this approach:
Build a string with the data that I wanted from the form. just me.control1 & " % " & me.control2 & etc. etc.
use InsertInto to put that in a table, also put NOW into the next column of the table - new table called tblExport
make a new query which pulls only the last entry - as opposed to deleting the record later - six of one , half a dozen fo the other
Then export THAT new query to the Excel. Should work but the latest travail is that access can't find the table despite the fact that
it is successfully inserting the above data into the new table.

Please let me know if you have any more questions.

I should have said that access can't find the new Query I made off the new table. I did try just the table earlier but got the same type error message, it couldn't find the table.