Update Values(Open Spreadsheet 46 Times) on Macro Start

[paulcook]

I have written a macro in Excel 2003, Windows XP, that takes a spreadsheet exported from Crystal Reports so that I can add more data from another spreadsheet and perform some calculations.

I have written VBA to do all of this but when I open the exported Excel spreadsheet and launch the macro I get this Update Value message window and have to open the linked Excel file for each reference to it in the VBA. Everything works just fine AFTER you open the file 48 times!!

What can I do so that the user just opens the spreadsheet runs the macro and gets the completed spreadsheet? I have read many posts about using MS Query to get the data from the other spreadsheet but I found that you can't do this on an individual cell basis but for a group of cells only. In other words I can't say that cell E5 & E6 in my exported spreadsheet are equal to cells B2 & C2 in the sheet that I am getting additional data from and then skip and say that cells E8 & E9 are equal to cells D2 & E2.

TIA,

Paul

 

[SkipVought]

Hi,

Try using Application.DisplayAlerts=false in your code before the Open statement.

End you code with Application.DisplayAlerts=true

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[paulcook]

SkipVought,

While that code did bypass the display of the window, that gave me the Update Values message and forced me to open the spreadsheet once for every reference to it in the VBA, it did not solve my problem. Now all of the references to the cells in the other spreadsheet just show the REF error.

I think that I might try to use the macro to import the data, using MS Query, from the other spreadsheet into a new sheet, then add a formula that references that sheet. That should solve my issue. I have used the import data wizard thingy successfully with the other sheet closed. After I am done I can just copy and paste special for values and then delete the sheet that I imported the data on.

Regardless of what happens thanks for taking the time to help me out. I can't tell you how helpful tek-tips has been me when I bump up against the walls of my knowledge in Access, VBA, Crystal Reports and Excel.

Thanks,

Paul