• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 67
  • Last Modified:

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
0
ES-Components
Asked:
ES-Components
  • 8
  • 7
1 Solution
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now