Excel update causing Macro problem

Hi guys,

A recent update to our Microsoft office from 2016 to 2021 has caused an issue with one of our Macros.


What this macro is supposed to do is copy line 4 and paste it into line 5 for multiple files. It's still doing that but any line from lines 1 Through to 20 that has numeric values only is being shifted to the left and not lining up with anything else. This causes issues when trying to import it into our programs.

We have tried contacting the original author of the macro and he is stumped. We have also changed a few things (starting line, finish line etc.) but we are definitely not advanced in this knowledge.

Any help with what we can change here to make this work would be greatly appreciated.

I have attached a picture and highlighted the changes causing problems as well as added the code below.
Cheers!!

Please Login or Register  to view this content.

The code is a bit clunky and looks like it was based on the macro recorder so it's a little hard to unravel in the abstract. Is there any possibility of providing sample data files so that we can run this code and reproduce the results?

It is possible that the Excel upgrade is a coincidence and something else has changed in your environment or data files to cause this. There is rarely impact to VBA from one version to the next.

Originally Posted by Jaspermuduvhadzi

Hi all, how come the macros only works on the desktop and not on the sharedrive

Administrative Note:

Welcome to the forum Jaspermuduvhadzi.

We are happy to help, however it appears that your question is not related to this thread.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Here is the problem, I don't have a solution yet.

You are reading in a text file. The numeric values (starting on line 11) are right-justified by inserting spaces at the beginning. When Excel opens this file, those values are converted to numeric. Then when Excel writes them back, they are written with no justification, just the numbers. Excel does not know how to blank-pad numeric values for right-justification in a text file.

One approach is to force Excel to read everything as text, instead of converting to numbers. I do not know how to do that, but it may be possible.

Another option is to process the files as text files instead of opening them in Excel. This would require a total rewrite of your code.

I'll see what else I can offer.

Also, this seems unnecessary

Please Login or Register  to view this content.

You are opening the same file twice, not sure why.

Originally Posted by Marc L

Nope, your original code replaces line #6 with line #5 … So weird to use Excel just for that !

If the author of the code did well understand what is a text file
so he did not open it under Excel to modify it so your issue can't never arise !

So removing the useless, according to your first text file attachment a VBA basics demonstration for starters :

PHP Code: 

Sub Demo1()
        Dim V, D$, R%, W%, F$, L%, S$
        Set V = CreateObject("Shell.Application").BrowseForFolder(0, vbLf & "Source folder :", 1, "")
         If V Is Nothing Then Exit Sub Else V = V.Self.Path & "\"
        D = V & "NC Edited\":  If Dir(D, 16) = "" Then MkDir D
        R = FreeFile
        W = R + 1
        F = Dir$(V & "*.NC")
  While F > ""
        Open V & F For Input As #R
        Open D & F For Output As #W
    For L = 1 To 5
        If EOF(R) Then Exit For
        Line Input #R, S
        Print #W, S
    Next
        If L = 6 Then Print #W, S: If Not EOF(R) Then Line Input #R, S
     Do Until EOF(R)
        Line Input #R, S
        Print #W, S
     Loop
        Close #W, #R
        F = Dir$
  Wend
        Application.Speech.Speak "Done!", True
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Hi Marc L,

Haha yeah seems a little trivial to use excel for something so small but it saves us hours of time going through hundreds of files a day manually.
Straight off the bat this code is alot faster which is exciting. I just can't seem to get it to copy the files into the edited folder.

Cheers

I think what Marc is trying to say is that his rewrite of code is just using VBA to read and write a text file, which you could do in any language. It is not using any Excel files or Excel features.