Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I create a Macro that can update a sheet?
Hi all,
I am trying to create a macro that can update the entire sheet. I have this data entry sheet and I also have a data.xls file that is converted from a text file. This data.xls file changes every month. So, instead of typing all those numbers every month into the data entry sheet, which is a different workbook, I want to create such a macro that when I run it, it should load all the numbers for the new month into the data entry sheet. If I make a user form in Macro and add a button and a text box to it, what would the overall code be? This way, if the user wants to see a certain month, he types it into the text box and clicks on the button and the sheet will load the numbers of that month. You can email me I would be so happy if you guys help me out. Thank you ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I create a Macro that can update a sheet?
There is a good example at
http://www.exceluser.com/biz/h_vba_textcols.htm. VBA Help also has pretty good documentation on the functions & statements you need to use to read from text files (Open, Input, EOF, etc). Regards, Steve cimbom wrote: Hi all, I am trying to create a macro that can update the entire sheet. I have this data entry sheet and I also have a data.xls file that is converted from a text file. This data.xls file changes every month. So, instead of typing all those numbers every month into the data entry sheet, which is a different workbook, I want to create such a macro that when I run it, it should load all the numbers for the new month into the data entry sheet. If I make a user form in Macro and add a button and a text box to it, what would the overall code be? This way, if the user wants to see a certain month, he types it into the text box and clicks on the button and the sheet will load the numbers of that month. You can email me I would be so happy if you guys help me out. Thank you ! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I create a Macro that can update a sheet?
Here's an example that will get you started. I've used Excel's build in
GetOpenFileName function to allow the user to select the desired source data sheet. There is a separate sub routine that does all the work - "LoadData". This takes two parameters- the fulll path of the excel file to use as the data source, and a worksheet object to copy the data to. This sub routine can then be called from a main sub e.g. Demo_LoadData. Sub LoadData(strSourceFilePath As String, wsDest As Worksheet) '''Open file and copy data from file to wsDest Dim wsSource As Worksheet 'speed things up and hide process from user Application.ScreenUpdating = False 'open source file and set object variable to first worksheet in workbook Set wsSource = Application.Workbooks.Open(strSourceFilePath).Work sheets(1) 'copy/duplicate data from Source sheet to Dest sheet wsDest.Range("A2").Formula = wsSource.Range("B10").Formula wsDest.Range("C5").Formula = wsSource.Range("D2").Formula 'etc... 'tidy up wsSource.Parent.Close savechanges:=False End Sub Sub Demo_LoadData() Dim strS As String 'prompt user to select an Excel file strS = Application.GetOpenFilename( _ filefilter:="Excel files (*.xls),*.xls", _ FilterIndex:=1, Title:="Select data file to load:") 'call sub to open file and load data to active worksheet LoadData strS, ActiveSheet End Sub Cheers, Dave "cimbom" wrote: Hi all, I am trying to create a macro that can update the entire sheet. I have this data entry sheet and I also have a data.xls file that is converted from a text file. This data.xls file changes every month. So, instead of typing all those numbers every month into the data entry sheet, which is a different workbook, I want to create such a macro that when I run it, it should load all the numbers for the new month into the data entry sheet. If I make a user form in Macro and add a button and a text box to it, what would the overall code be? This way, if the user wants to see a certain month, he types it into the text box and clicks on the button and the sheet will load the numbers of that month. You can email me I would be so happy if you guys help me out. Thank you ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a macro on a template update with new each new sheet create | Excel Discussion (Misc queries) | |||
To create an Macro and alco to update an graph | Excel Discussion (Misc queries) | |||
How can I have a macro update the sheet? | Excel Discussion (Misc queries) | |||
Macro to update sheet | Excel Discussion (Misc queries) | |||
Macro to Create a Chart and Update it Automatically | Charts and Charting in Excel |