Problem pulling excel data from another workbook

csukardi151

New Member
Joined
Jun 19, 2012
Messages
6
Hello,

I'm having a crystal report automatically export to excel once a day. I wanted to create a new excel workbook that pulled certain data from this file and create some graphs. I am using the formula:

"='s:\folder\[excel.xls]Sheet1'!A1"

Here's the catch, this formula does not work right away. In order to get it to work, I need to open the excel document that was exported from crystal reports and click the save button (without making any changes). Once I did that, the formula will work no problem.

Is there a way I could get around this? An alternative way to do what i'm doing? I really just want to create an excel file with a few graphs that is automatically updated when opened, which is why I wanted it to pull data from the exported file that is updated once a day.

Thanks in advance,

Steve
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
You could build a macro that runs every day that opens the generated file and saves it, then closes it so you can access it. If you're not familiar with VBA, I can help you out.

-Austin
 
Upvote 0
If it gets the job done! I am a bit unfirmiliar with VBA, never dabbled in it. I'm guessing I would really just need the script? Thanks for your reply Austin.
 
Upvote 0
Steve,
Right-click on a spreadsheet tab in your "master workbook", click view code. This will take you to the Visual Basic editor. Now Right-click on the name of your workbook on the left hand side and click Insert -> Module. Paste the following code and change the file path.

Code:
Sub FileSave()Dim ExcelFile As String


Application.DisplayAlerts = False
ExcelFile = "s:\folder\excel.xls"
Workbooks.Open (ExcelFile)
ActiveWorkbook.SaveAs Filename:=ExcelFile, FileFormat:=xlNormal
ActiveWorkbook.Close
Application.DisplayAlerts = True


End Sub

You can easily create a button on your spreadsheet to run the macro as well...see this link. An alternate way to get to the button button (that sounds redundant) is to right click on any of the toolbars in Excel and click Forms.
http://www.mrexcel.com/tip068.shtml

Let me know how it goes.

-Austin
 
Last edited:
Upvote 0
Thanks Austin, I just gave this a try. I am coming up with an error "Compile error:" "Expected: end of statement".

It has Dim highlighted in the very first line. Is there more I need to change other than the file location? Again, thanks for your help.

~Steve
 
Upvote 0
I actually put "Dim ExcelFile As String" on a new line by itself. That got rid of any errors with the code. However, it did not work.
 
Upvote 0
I actually put "Dim ExcelFile As String" on a new line by itself. That got rid of any errors with the code. However, it did not work.

Typo on my part, sorry. As for why it's not working, I'm not sure. Try stepping through (F8) and see if each step seems to work.
 
Upvote 0
Code is working fine, I even see the time stamp change on the file when I go through the debugging process. It isn't working when I just open the excel document, it just opens as if there is no code. I have the document saved as .xlsm.
 
Upvote 0
Steve,
I may have misunderstood you at the very beginning. Are you going to be running this macro from an existing workbook? If so, do you want it to run every time you open that workbook? I'm trying to get a mental picture of your workbooks and how they're interacting.
 
Upvote 0
Yes that is correct. I will have an existing main workbook that will be pulling the updated data from the workbook that is getting updated (replaced) daily. Each time that data workbook is replaced, it needs to be opened and saved before the formula's work correctly in the existing main workbook. So the macro would need to run every time the main workbook is opened just in case the data workbook had been recreated. Sounds a bit confusing and I apologize. If you still don't understand, I can try explaining it in more details.
~Steve
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top