How can I create a Macro that can update a sheet?

[cimbom]

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 @[email protected]
I would be so happy if you guys help me out. Thank you !

 

[wisccal]

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

 

[Dave Ramage]

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).Worksheets(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