Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Turn off calc from VBA (2002 SP2)

    I have a macro that imports 5 csv files into an existing workbook. They provide the base data for all of the calculations and to speed things up, I've been trying to turn the auto calc feature off while the five files are imported. This seems to work for the first file but is ignored for the rest. If I step through the macro, the first file is opened and moved into the workbook, the second import triggers a calculation but if I stop the macro and check the setting on the workbook, the manual calc box is still checked.
    Any ideas??

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Turn off calc from VBA (2002 SP2)

    Hi Joe
    Are there links involved. IE, when you import the second file, does it have in any way some link to the first?
    Regards,
    Rudi

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn off calc from VBA (2002 SP2)

    The steps I go through within the macro are as follows

    Open the first csv file
    Move the csv file into the workbook
    Select all active cells and use create names to use the column headings as the range names - the formulas in the body of the workbook refer to these named ranges
    Repeat for remaining 4 csv files

    All of the sheets are linked to the main part of the workbook but not to each other. They are within the workbook (ie., not external links if you know what I mean). What has me puzzled is that the process appears to work for the first sheet but not for the next one.
    Each of the CSV files

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Turn off calc from VBA (2002 SP2)

    I have no answer for you. This is puzzling. This might be a silly statement, but have you thought of first importing all the files, then using create names to set up name ranges. Try it this way around, maybe it will work!!?
    Let me know!
    Regards,
    Rudi

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Turn off calc from VBA (2002 SP2)

    Hi jsquared,

    Perhaps something like the following would work:

    Sub FileImport()
    Dim DataStr As String, FileName As String
    Dim FileNum As Integer, i As Integer
    Dim Counter As Double
    With Application
    .ScreenUpdating = False
    .Calculation = xlManual
    .DisplayAlerts = False
    End With
    For i = 1 To 1
    FileName = "C:My Documents" & (i) & ".CSV"
    'Get Next Available File Handle Number
    FileNum = FreeFile()
    'Open File For Input
    Open FileName For Input As #FileNum
    With Worksheets(i)
    Counter = 1
    'Loop Until the End Of File Is Reached
    Do While Seek(FileNum) <= LOF(FileNum)
    'Display Importing Row Number On Status Bar
    Application.StatusBar = "Importing file: " & FileName & ", Row: " & Counter
    'Store One Line Of Text From File To Variable
    Line Input #FileNum, DataStr
    'Update Data In Active Cell
    With Range("A" & Counter)
    .Value = DataStr
    .TextToColumns Destination:=Range("A" & Counter), DataType:=xlDelimited, Comma:=True
    End With
    Counter = Counter + 1
    Loop
    'Close The Open Text File
    Close
    'Remove Message From Status Bar
    Application.StatusBar = False
    End With
    Next
    'End Macro
    With Application
    .ScreenUpdating = True
    .Calculation = xlAutomatic
    .DisplayAlerts = True
    End With
    End Sub

    You'll need to modify filepaths & names & worksheets IDs to suit, but it might (hopefully) stop the recalc because there's no copying & pasting going on.

    I'd also question why you're updating the range names, since they could remain unchanged if the new data occupy the same ranges or if you cleared all the existing data first (the above code assumes the former). In any event, you might find that your problem would be fixed if you left redefining the ranges until after all of the data have been loaded.


    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn off calc from VBA (2002 SP2)

    For the time being, I've given up.

    The interesting thing is that when I step through the code my earlier comment isn't right. The calculation begins when I try to open the third file (not the second) , the line that actually triggers the calculation is

    Workbooks.Open Filename:="C:mydirectory" & datafile & ".csv"

    the line has been used twice without calculating.

    I might come back to it when I get a bit of spare time.

  7. #7
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn off calc from VBA (2002 SP2)

    Macropod,
    Thanks for the input. I have tried using this approach in the past but it seemed far slower than bringing the csv file in directly.

    With regards to your second point, the files I'm importing are in csv format and I need to name the ranges in those files. They are effectivley 'clean' every time I open them. (They are outputs from another program and are generated every time that program runs)

    Thanks again

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn off calc from VBA (2002 SP2)

    When I use VBA to set Calc to Manual I include an error handler that will switch it back to Automatic if the routine fails for some reason.

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Turn off calc from VBA (2002 SP2)

    Hi jsquared,

    I don't understand why, if you're importing the data from the csv files, you should need to name ranges in the csv files themselves - I would have thought that naming the imported range would have been the way to go.

    But, since you seem to be naming ranges in the cdsv files, I suspect that the recalc is related to that. If so, you could probably defer the re-calc until all of the csv files have been processed by not closing any of them until you've finished iimporting the data and naming the ranges. You might also try experimenting with settings like 'recalculate before save', 'update remote references' and 'save external link values', all of which are found under Tools|Options|Calculation - I suspect one or more of these is causing the recalc.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •