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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

soccerkingpilot

Board Regular
Joined
May 21, 2012
Messages
105
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
 

csukardi151

New Member
Joined
Jun 19, 2012
Messages
6
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.
 

soccerkingpilot

Board Regular
Joined
May 21, 2012
Messages
105
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:

csukardi151

New Member
Joined
Jun 19, 2012
Messages
6

ADVERTISEMENT

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
 

csukardi151

New Member
Joined
Jun 19, 2012
Messages
6
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.
 

soccerkingpilot

Board Regular
Joined
May 21, 2012
Messages
105

ADVERTISEMENT

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.
 

csukardi151

New Member
Joined
Jun 19, 2012
Messages
6
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.
 

soccerkingpilot

Board Regular
Joined
May 21, 2012
Messages
105
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.
 

csukardi151

New Member
Joined
Jun 19, 2012
Messages
6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,255
Messages
5,600,554
Members
414,388
Latest member
Pkmep4

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
Top