Replies

Thanks for the suggestion, Wendell. However, TransferSpreadsheet won’t work for me since the data is starting in Access. It is pulled into Excel (or sent there) supplying data for an already formated chart, then pulled back into Access. If I Export it, a new spreadsheet (or even file) will be created and the chart won’t exist. I don’t see a way to tell Access to overwrite an existing range. Is there a way to do this?

I have no experience creating an Excel chart from automation. If there is no easier way, I can look into this.

Thanks for the suggestion, Wendell. However, TransferSpreadsheet won’t work for me since the data is starting in Access. It is pulled into Excel (or sent there) supplying data for an already formated chart, then pulled back into Access. If I Export it, a new spreadsheet (or even file) will be created and the chart won’t exist. I don’t see a way to tell Access to overwrite an existing range. Is there a way to do this?

I have no experience creating an Excel chart from automation. If there is no easier way, I can look into this.

390 characters including the criteria string. Criteria string is 84 characters long. I realize that there is a limit to how long the SQL string can be, but surely I’m not close. I know (or at least I think I know) that I’ve had other queries with longer SQL strings.

390 characters including the criteria string. Criteria string is 84 characters long. I realize that there is a limit to how long the SQL string can be, but surely I’m not close. I know (or at least I think I know) that I’ve had other queries with longer SQL strings.

I agree! One thing to look out for: During a previous project, I tried to use Word as a report writer, which OLE’d a chart from Excel, which in turned pulled it’s data from Access, which in turn pulled it’s data from an AS/400. Quite a data path. The solution included some code within Word to force Excel to update it’s data from Access. It worked, except it would partially corrupt the Excel file. By partially, I mean that the solution would produce the intended results, but the Excel file could no longer be opened and therefore it could not be edited if something changed or needed to be added. If you tried to open the Excel file, nothing would happen–No error message or anything. I finally abandoned that solution.

I agree! One thing to look out for: During a previous project, I tried to use Word as a report writer, which OLE’d a chart from Excel, which in turned pulled it’s data from Access, which in turn pulled it’s data from an AS/400. Quite a data path. The solution included some code within Word to force Excel to update it’s data from Access. It worked, except it would partially corrupt the Excel file. By partially, I mean that the solution would produce the intended results, but the Excel file could no longer be opened and therefore it could not be edited if something changed or needed to be added. If you tried to open the Excel file, nothing would happen–No error message or anything. I finally abandoned that solution.

Thanks Steve and Hans for your input.

Hans, it appears that MSChart expects data with series in columns. My data is coming in with series in rows. I couldn’t figure out how to MSChart to read it the other way. If anyone knows how to do this, I would love to hear it. I can’t change the orientation of the data because Access is reading directly from our AS/400. That is my problem with MSChart.

Your idea regarding Past Special… did the trick, though. That comment prompted me to pull the data into Excel, create a chart, then Paste Special it back into Access. I just got through with this when Steve’s post came across with the exact same solution I just used. Steve–it worked like a charm! There is even a parameter in the base query that I thought I would have to use code to get Excel to update the data as the parameter changes, but it gets propagated automatically. Way cool.

Thanks Steve and Hans for your input.

Hans, it appears that MSChart expects data with series in columns. My data is coming in with series in rows. I couldn’t figure out how to MSChart to read it the other way. If anyone knows how to do this, I would love to hear it. I can’t change the orientation of the data because Access is reading directly from our AS/400. That is my problem with MSChart.

Your idea regarding Past Special… did the trick, though. That comment prompted me to pull the data into Excel, create a chart, then Paste Special it back into Access. I just got through with this when Steve’s post came across with the exact same solution I just used. Steve–it worked like a charm! There is even a parameter in the base query that I thought I would have to use code to get Excel to update the data as the parameter changes, but it gets propagated automatically. Way cool.

Interesting discussion. I think I got some direction to go. Thanks

Interesting discussion. I think I got some direction to go. Thanks

I have created a Word document with a linked Excel chart. There is code that updates the data for the chart automatically. After I run this code within Word, the excel file that contained the data and the chart is no longer accessible. I cannot open it for further editing. Interestingly, though, running the apparently offending macro in Word still accesses the Excel chart and updates the data like it is suppose to. How weird is that?? Is there something else I should have done in order to release that file?

Here is the code I ended up with:
[indent]

---

Private Sub Document_Open()

Dim wbk As Object

Set wbk = GetObject(“C:test1.xls”)
With wbk
.Sheets(“sheet1”).Range(“A2”).QueryTable.Refresh
.Sheets(“chart1”).Select
.Close (True)
End With

Set wbk = Nothing
End Sub

---

[/indent]

Steve–

It seems that I had macro security set to high so the macro would not run. Interesting that I did not get a warning, however. Thanks for your help, your code was useful in helping me to debug mine. Now I get to read up on how to sign a macro. It works like a charm now, except for the security setting.

Steve–

It seems that I had macro security set to high so the macro would not run. Interesting that I did not get a warning, however. Thanks for your help, your code was useful in helping me to debug mine. Now I get to read up on how to sign a macro. It works like a charm now, except for the security setting.

Thanks again Steve. However, I don’t think the Document_Open procedure is firing. I tested this by clearing the procedure and just adding a Msgbox, which did not appear with the opening of the document. Am I right — that this procedure is suppose to fire upon opening the document? Is there something else that I need to do?

Thanks again Steve. However, I don’t think the Document_Open procedure is firing. I tested this by clearing the procedure and just adding a Msgbox, which did not appear with the opening of the document. Am I right — that this procedure is suppose to fire upon opening the document? Is there something else that I need to do?