+ Reply to Thread
Results 1 to 6 of 6

Importing File with VBA really really slow

  1. #1
    davith
    Guest

    Importing File with VBA really really slow

    Hello,

    I got a VBA script from this group to import files with VBA and it has
    worked like a charm until yesterday. Now the processor stays pegged @
    100% utilization when running the script and the import takes forever..



    I thought it was a samba issue since I was pulling files from a mapped
    drive, but I copied them locally and get the same results.

    Any ideas?

    script for reference:



    Public Sub re_import_files()
    Dim even As Integer
    Dim mod2 As Integer
    even = 2
    For Each Cell In Selection
    ' mod2 = (even - 2) * Int(even / 2)
    mod2 = even Mod 2
    even = even + 1
    If mod2 = 0 Then
    Sheets(Cell.Value).Activate
    ' AddSheet Cell.Value, "Template"
    Else
    ImportTextFile Cell.Value, Chr(9)
    End If
    Next
    End Sub


    Public Sub ImportTextFile(Fname As String, Sep As String)

    Dim RowNdx As Integer
    Dim ColNdx As Integer
    Dim TempVal As Variant
    Dim WholeLine As String
    Dim Pos As Integer
    Dim NextPos As Integer
    Dim SaveColNdx As Integer

    Application.ScreenUpdating = False
    'On Error GoTo EndMacro:

    'SaveColNdx = ActiveCell.Column
    'RowNdx = ActiveCell.Row

    SaveColNdx = 1
    RowNdx = 1


    Open Fname For Input Access Read As #1

    While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
    WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
    TempVal = Mid(WholeLine, Pos, NextPos - Pos)
    Cells(RowNdx, ColNdx).Value = TempVal
    Pos = NextPos + 1
    ColNdx = ColNdx + 1
    NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
    Wend

    EndMacro:
    On Error GoTo 0
    Application.ScreenUpdating = True
    Close #1

    End Sub



    Sub AddSheet(Sname As String, Fname As String)
    Sheets.Add After:=Sheets(Fname)
    ActiveSheet.Name = Sname
    End Sub



    Sub AddSheets()
    For Each Cell In Selection
    'Sheets.Add After:=Sheets("Template")
    'ActiveSheet.Name = Cell.Value
    AddSheet Cell.Value, "Template"
    Next
    End Sub


  2. #2
    Mark Lincoln
    Guest

    Re: Importing File with VBA really really slow

    Have you installed any new software, updated any old software, or
    changed software settings (especially concerning antivirus and other
    security software) prior to the slowdown?


  3. #3
    davith
    Guest

    Re: Importing File with VBA really really slow

    That is a good point, but unfortunately I haven't changed anything.


  4. #4
    davith
    Guest

    Re: Importing File with VBA really really slow

    That is a good point, but unfortunately I haven't changed anything.


  5. #5
    Mark Lincoln
    Guest

    Re: Importing File with VBA really really slow

    Okay, I'll guess again:

    Is there anything markedly different about the text file(s) you're
    importing? Size? Content?

    Can you test importing a file that worked before, using a copy of the
    workbook?

    Have you checked for possible viruses, spyware, and the like?

    You could try booting in Safe Mode and running the macro. If it runs
    as it used to, then something is loading on a normal boot that is
    interfering with it. You can then try ending nonessential processes in
    the Task List to see if one of them makes the problem disappear.


  6. #6
    davith
    Guest

    Re: Importing File with VBA really really slow

    text files are the nearly identical. Numbers maybe different.
    I will try the Task List/ Safe Mode idea
    Thank you.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1