How to create an Excel Macro to Open A File and Refresh the data?

In Excel 2016, I need a Macro to do the following: Open an Excel file, Refresh ALL the data-links, Save as another file (Renamed), close that file, and then
Save and close the original file that was opened. (How does the Macro know when ALL the data has been refreshed?) Is there an easy way to do this?

Thank you in advance for any help.
Rick
ES-ComponentsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...

Sub RefreshAllAndSaveAs()

Dim swb As Workbook
Dim fileName
Dim filePath As String

Application.ScreenUpdating = False

'Prompt user to select a file
fileName = Application.GetOpenFilename("All Excel Files ,*.xl*", , "Select A File To Open")

'If Cancel was hit by the user
If fileName = False Then
    MsgBox "You didn't select any file.", vbExclamation
    Exit Sub
End If

'Opening the file
Set swb = Workbooks.Open(fileName)

'Path where new file will be saved
filePath = swb.Path & "\"

'Constructing unique filename
fileName = swb.Name
fileName = WorksheetFunction.Replace(fileName, InStr(fileName, "."), 255, "")

fileName = fileName & "_" & Format(Now, "hhmmss") & ".xlsx"

'Refreshing All in the opened file
swb.RefreshAll
DoEvents

'Saving the original file
swb.Save

'Saving original file with new file name
swb.SaveAs filePath & fileName, 51

'Closing the new file with all the changes
ActiveWorkbook.Close True

Application.ScreenUpdating = True
End Sub

Open in new window

0
ES-ComponentsAuthor Commented:
Subodh,
Your solution looks what I need. I would like to make this even simpler with the same logic as I originally stated.  I am not a programmer,
so I need the code such as I can just copy and paste into a Macro. I will give you the file names.
I do not need to be prompt for anything. I will change to an Auto-Exec Macro.

The original file to be opened is called 'LateOrdersFORMULA.xlsm'  (There are 4 tabs that need to be completely refreshed)
After the refresh is completely done, save the file as 'RefreshDone.txt'
Then save and close the original file ('LateOrdersFORMULA.xlsm')

Thank you in advance for all your help!!!
Rick
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The original file to be opened is called 'LateOrdersFORMULA.xlsm'  (There are 4 tabs that need to be completely refreshed)
That can be done.

The original file to be opened is called 'LateOrdersFORMULA.xlsm'  (There are 4 tabs that need to be completely refreshed)
After the refresh is completely done, save the file as 'RefreshDone.txt'
What do you mean by save as "'RefreshDone.txt"?
Do you want to save the file as Text file?
As you said, the source file will have 4 sheets in it so you will need to save 4 sheets as text files with different names if you want to save them all.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

ES-ComponentsAuthor Commented:
When it does the refresh and it is complete, just save the entire file (with all 4 tabs) and name it 'RefreshDone.txt'
I will send you a copy of my original excel file with formulas and the 4 tabs if this will help you.  I always want this file to be linked to the
data file.

Thank you
Rick
LateOrdersFORMULA.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
What do you mean by save as "'RefreshDone.txt"?
Do you want to save the file as Text file?
As you said, the source file will have 4 sheets in it so you will need to save 4 sheets as 4 text files with different names if you want to save them all.

You didn't answer my query?
0
ES-ComponentsAuthor Commented:
After refreshing, I want save the entire file as a text file with one name.  I also want to save the original file.
Thank you...
Rick
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
When you save as Text file, it saves only one sheet as Text file.
Also if you create a text file and write all the data from all the four sheets into it, the data on all the 4 sheets have different number of columns with different headers, how that would help you?

Can you create one Text file manually considering the existing data on those four sheets and upload it here?
0
ES-ComponentsAuthor Commented:
I gave you the original file. Refresh that file, and save it. Then, I guess according to you, I need to save 4 separate text files from the four tabs in the original file. You can use the names of the 4 tabs as the names of the 4 new text files.
I hope this explains what I need.

Rick
0
ES-ComponentsAuthor Commented:
Or 1. Refresh the file. 2, Just save the first tab as a text file. You could copy and past special the first tab only after refreshing and save just that tab as a text file. Then save the entire original file, and the macro would be complete.

I believe that will work as I REALLY only need a copy of the very first tab.

Hope this helps...

Rick
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay, please give this a try...

Sub RefreshAllAndSaveAs()

Dim swb As Workbook
Dim sws As Worksheet
Dim sourceFile As String
Dim saveAsPath As String
Dim saveAsFileName As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'******************************************************************************
'Change the values of following three variables as per your requirement
sourceFile = "C:\Users\YourUserName\Desktop\LateOrdersFORMULA.xlsm"
saveAsPath = "C:\Users\YourUserName\Desktop\"    'Path where new file will be saved
saveAsFileName = "RefreshDone.txt"  'Name of the text file
'******************************************************************************

'Opening the file
Set swb = Workbooks.Open(sourceFile, False)
Set sws = swb.Sheets("OpenOrdersLateCP")

'Refreshing All in the opened file
swb.RefreshAll
DoEvents

'Saving the original file
swb.Save
sws.SaveAs saveAsPath & saveAsFileName, xlTextWindows

'Closing original file
swb.Close True

Application.ScreenUpdating = True
End Sub

Open in new window

0
ES-ComponentsAuthor Commented:
Subodh,
I tried to do this and it does not work. I am NOT a programmer at all. I know just enough to get myself in trouble. I usually just record a Macro and not worry about the code.
I assume I put this under the VBA Project, This Workbook. See attached image. If it goes there, I cannot get it to work. So...
if you could help me out by giving the exact code and exactly where to place this I can award you the solution prize.

Here is the location of the Source file: \\Server\my documents\Employee Documents\Rick G Documents\LateOrders\LateOrdersFormula.xlsm
Here is the SaveasPath and SaveAsFileName: \\Server\my documents\Employee Documents\Rick G Documents\LateOrders\RefreshDone.txt

Where do these go in the code?

Also, I want this to be an Auto-Run Macro. Where do these lines of code go in your code? I assume I need these

Sub Auto_Open()
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Application.OnTime Now + TimeValue("00:00:02"), "Save_Exit"
Sub Save_Exit()
Application.Quit
ThisWorkbook.Close SaveChanges =True
End Sub

Thank you...
Rick

Excel VBA ProjectExcel VBA Project
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Code on Standard Module like Module1:

Sub RefreshAllAndSaveAs()

Dim swb As Workbook
Dim sws As Worksheet
Dim sourceFile As String
Dim saveAsPath As String
Dim saveAsFileName As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'******************************************************************************
'Change the values of following three variables as per your requirement
sourceFile = "\\Server\my documents\Employee Documents\Rick G Documents\LateOrders\LateOrdersFormula.xlsm"
saveAsPath = "\\Server\my documents\Employee Documents\Rick G Documents\LateOrders\"    'Path where new file will be saved
saveAsFileName = "RefreshDone.txt"  'Name of the text file
'******************************************************************************

'Opening the file
Set swb = Workbooks.Open(sourceFile, False)
Set sws = swb.Sheets("OpenOrdersLateCP")

'Refreshing All in the opened file
swb.RefreshAll
DoEvents

'Saving the original file
swb.Save
sws.SaveAs saveAsPath & saveAsFileName, xlTextWindows

'Closing original file
swb.Close True

Application.ScreenUpdating = True
End Sub

Open in new window


Code on ThisWorkbook Module:

1) If you want to run the code "RefreshAllAndSaveAs" after specific time interval after opening the file, place the following code on ThisWorkbook Module.

Private Sub Workbook_Open()
'The code "RefreshAllAndSaveAs" will run automatically after 2 seconds once the file is opened
Application.OnTime Now + TimeValue("00:00:02"), "RefreshAllAndSaveAs", , True
End Sub

Open in new window


2) If you want to run the code "RefreshAllAndSaveAs" at specific time after the file is opened, place the following code on ThisWorkbook Module.

Private Sub Workbook_Open()
'The code "RefreshAllAndSaveAs" will run automatically on 5 PM if file is open
Application.OnTime TimeValue("17:00:00"), "RefreshAllAndSaveAs", , True
End Sub

Open in new window


Note: On ThisWorkbook Module, you should place only one code of the above two codes.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ES-ComponentsAuthor Commented:
SubodH,
We're almost there!!!  I think I got it to work. Your code opens the source, refreshed the data, and saved a text file. Perfect!!!!

The final question is how do I get the Macro to run automatically??? Currently, I have to open the excel file and click "Run" macro where your code is and it works. All I want now it to be able to do is "On Open" run your Macro code automatically and when done, close the excel file.
I DO NOT want to have to click the button to run your code.

Rick
0
ES-ComponentsAuthor Commented:
Great job!!! Thank you for all your help. I figured out the last part of how to automate your Macro code.
Thanks again for all your patience and help!!!!!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.