Adjust data range without opening multiple excel files

[Len]

Hi,

Codes below copied from the forum are adjusted to suit my need but I
have a problem to run the codes each time will open an excel file
which will take a few minutes particularly when there are >20 excel
files.

Is there a better way to run the codes without opening the excel file
and save the changes in another folder ? so that I do not have to
spend much time to run > 20 excel files


Sub ChgHeader()

Application.Calculation = xlCalculationManual

Dim wb As Workbook
Dim WBName As String
Dim WhatFolder As String

WhatFolder = "M:\CA\SP\Bdgt\BAl\dem3\"
ChDrive WhatFolder
ChDir WhatFolder
WBName = Dir("*.xls", vbNormal)
Do Until WBName = vbNullString
ChDir "M:\CA\SP\Bdgt\BAl\dem3"
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb = Workbooks.Open(WBName)
wb.Worksheets("P+L").Select
Dim i As Long
Dim Lstrow As Long
Lstrow = Cells(Rows.Count, "A").End(xlUp).Row
If Lstrow > 0 Then
For i = 5 To Lstrow
If Cells(i, 1).Value <> "" Then
Cells(i, 1).Copy
Cells(i, 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End If
Next
Else
MsgBox "It appears that the file is empty, check the file again"
Exit Sub
End If
ChDir "M:\CA\SP\Bdgt\BAl\dem4"
wb.SaveAs Filename:=Left(WBName, InStrRev(WBName, ".") - 1),
FileFormat:=xlNormal

wb.Close SaveChanges:=True
WBName = Dir()
Loop

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
'Application.EnableEvents = True

End Sub

Any helps will be much appreciated as I'm beginner to vba prog


Regards
Len

 

[Len]

Hi Joel,

Thanks for your advice, it will be great to use ADO to access, edit
excel file if I have time to do it
But due to time constraint to meet the deadline, for time being I need
to use back the above codes to run without opening excel files
I hope you will help me to modify my codes above just to meet the
deadline


Thanks & Regards
Len

 

[Len]

Joel,

Thanks for your quick reply.

It seems that it leave me no choice I have to use ADO
method............
It still very slow after I use your codes to test on one file and then
on whole directory

I'll work around on ADO method and see the progress
Thanks anyway

Regards
Len

 

[Len]

Joel,

I think I have a problem to use ADO method for several excel files
that had already been completed ( ie budget files already submitted
from 25 profit centers (BAI) and 22 cost centers (BAII) respectively
under budget directory(dem3) with 2 folders namely BAI and BAII
folders)

Now the problem is all budget files submitted with incorrect row
header format so I need to refill up the row header under column B in
one worksheet("P+L") from every budget file and thereafter I will
create named range in that "P+L" worksheet of every budget file. Later
I will proceed to create a summary budget via data consolidation

For data consolidate function, I will use keys selection of row,
column headers and create link to data source.

I'm in puzzle to which method is the most appropriate to run this
batch of excel files for data consolidation purpose, please advise

Regards
Len

 

[Len]

Joel,

Yep....Application.enable events already set to comment line ( ie turn
off ) and yet still no improvement


Regards
Len