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

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

Select allOpen in new window

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

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.

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

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?

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

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?

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

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

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

Select allOpen in new window

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

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

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!!!!!

You're welcome!