Posts by james8427

    Hi all

    I have a question.

    I have created a form, named: frm1
    This form is created in the file named: 123.xls

    I want frm1 to load the moment the file: 123.xls is opened. How do i go about doing that?

    I know something to do with formload or something?

    Hope to hear from you guys soon


    Hi all

    I have created several worksheets (Sheets(1) & Sheets(2)) on an excel file, let say 123.xls.

    You will find one chart on each of these 2 worksheets.


    I would like to link these 2 charts to a Powerpoint file. Such that, when i have amend these two charts in Excel, when i excecute my powerpoint file, I could refresh the chart and the new chart would appear.

    BEaring in mind that I need to compute these charts on a weekly basis, and find that its a hassle to manually cut and paste the charts. I knwo there's way to do it, but I have no idea how.

    Another thing is that I have also automate the tabulation of data to compute the chart. I have done that, but it is not shown in this attachment, because I am more concern about the linkage between powerpoint and excel.

    Hope that helps

    Cheers. hear from you guys soon.


    Hi WillR

    Thank you so much for your code

    It works perfectly fine.

    There's still some problem.

    1. Can i dun specify truefile = "C:\Phang Jian Qiang\Arantxa\forecast.txt"

    I want the truefile to the FILENAME only.
    Example: truefile = "forecast.txt"
    Reason: I want the user to have the flexibility of choosing the directory where the file forecast.txt resides.

    Is there a way?

    2. When the user selects the wrong file, the code will jump to EXIT SUB. That is perfectly ok. But, it would be better if the sequence can loop till the user selects the correct file.

    Reason: If this section of cods does not loop, after the first selection, it would Exit Sub, bearing in mind that, I have several macros fused together.

    Take a look at this:

    Sub Final()

    Application.Run "A.xls'!ImportForecast"
    Application.Run "A.xls'!Calculate"
    End Sub

    In this section of codes, I have two macros, ImportForecast and Calculate.

    The fact that when i run macro Importforecast. it would prompt to select the file name forecast.txt, if select wrongly, it would exit that macro AND continue on with macro calculate.

    If i dun have the file(Forecast.txt) in the spreadsheet, it would be meaningless to carry on with the next macro.

    So, i think it would be better to loop the macro ImportForecast till the user selects the correct file and then proceed on.

    Hope that helps.

    Thank you so so much!


    Hi all

    I have a question regarding 'verifying the filename'

    I want the excel to clever enough to prompt the user to choose the correct file instead of the wrong ones.

    This is my code:
    Sub ImportForecast()

    myfile = Application.GetOpenFilename("Text Files,*.txt")

    Workbooks.OpenText Filename:=myfile, _
    Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
    Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
    ActiveWindow.WindowState = xlNormal
    With ActiveWindow
    .Top = -7.25
    .Left = 160.75
    End With
    Before:=Workbooks("FCST HIT RATE.xls").Sheets(3)
    ActiveSheet.Move Before:=Workbooks("FCST HIT RATE1.xls").Sheets("Historical shipment")
    ActiveWindow.WindowState = xlMaximized
    End Sub

    I have specify for excel to only display txt files.

    Task, given that the freedom the user can choose the cirectory and select the desired file, i want excel to verify the filename before executing the rest of the macros.

    For example, the file name is 12345.txt
    The user would select the file 12345.txt, if select the correct file, it would proceed as per normal with the rest of the macro, if the user accidentally selects 6789.txt instead, which is the wrong file, i want excel to prompt the msgbox "WRONG FILE! PLS CHOOSE 12345.txt!", and then, the user can know that he/she select the wrong ones and can re-select. I dun wan excel to continue processing the file if the user selects the wrong one. It would be not meaningful to continue.

    Thank you. Cheers!

    james :yes:

    Hi Ivan, I need a code to hlp me with that

    I dun need to verify any constant, as the 5 files are all data files that needs formatting, hence, there's no need to identify for any constants.

    If the user accidentally selects ONE wrong file out of the 5, it would means, there's no point continuing, and with that would like to end the macro process instead of continuing.

    Hope that helps

    james :tumble:

    Hi all

    I have a queation.
    Let say i have a series of code... the code actually prompts the user to select the desired file, there are let say 5 files the user would need to chhose.

    I am worried that the user may accidentally choose the wrong ones, and though the user KNOWS that he selected the wrong one, i guess the macro would continue formatting the wrong data and that would be not meaningful and waste of time.

    I used to put On Error resume next. I know its wrong to put this because it simply carry on though there are errors.

    I know there would be a line of code that the user can actually stop the whole macro?

    Thanz alot!

    James :spin:

    Hi all pals..

    I have a question and I have uploaded the example as attached.

    I need to classify the dates according to months. The piece of data was extracted from SAP, hence, when i opened from Excel, its a Text Delimted file and i actually went about formatting it and also specify that it is a date property.

    What I have done before but not shown in this example spreadsheet...
    I actually formatted the cells for the date field... though it may appear Jul-03 for me, but its no way i could paste the data onto another cell, it would not be the date field, it would appear numbers for me, i used the Paste-Speical-Method. So for my case, i dun think it is very meaningful and useful.

    Now, is there another way?

    I need to based on the date, and classify it according to months with year. For instance, if the the date is 11.07.03, the result return would be 0703.

    The reason i need to get 0703 format is because, later on, i would need to used 0703 as one of my argument to perform some calculation.

    hear form you soon.

    james :tumble:

    Hi all

    I need help regarding rounding up of numbers.
    For instance...

    A = B/C
    Where B = 230, C =1000

    Hence, A = 230/1000 = 0.23
    I want Access to convert/roundup the data 0.23 to 1. It will round up instead of round down. How should i go about it?

    Hi all

    I have a question.
    I have attached the file below.

    Data from A:E are downloaded from a database.

    They are not formatted yet, hence i would need to format them. The formatted results for each individual column will be placed on F:H. For instance, I need to format my whole column of data for "D" and put it in "F".

    I used ctrl+shift+END to identify the range, but it doesnt recognise D column.. instead it recognise from ranges D:I.

    I need excel to be able to recognise the new input cell's whole colume to allow me to carry on task.

    Bearing in mind that I have alot of data.


    Hi Rennie

    Can you explain to me the parameters of the formulae?

    I am very interested. Where do you get that? Its amazing. Btw, i got the result. Thanks alot.


    Hi all
    I have attached a file.
    Pls take a look

    Column A is the raw data i pulled out from the database.

    I need to convert them into format in Column B.

    For instance, the raw data is 1.236,000
    it represents 1236
    I need excel to trim of all the back portion of ,000 and regognise 1.236 as 1236.

    Bear in mind that i would need to values for calculation. I tried dividing by 1000, dun think it helps.

    My raw data goes up to as high as 4500 records.