Do While? Or LBound Ubound?

[ozan efendi]

Hi, I have a macro, that my Book2 fetches datas from reference workbooks Book1 and Book3.

If i write to for loop 10 instead of 3, the code looks for Book4 to Book10, and just because this workbooks don' t exist in my path, the code gives an error eventually.

Is it possible to ignore this limitation (for example: For iCount= 1 to n) It doesn' t matter how many reference workbooks in my path? Can anyone explain with the codes?

Regards,

Ozan.


Option Explicit


Sub TransferData()
Dim wbStore, wbSource As Workbook, wsStore, wsSource As Worksheet, LastRow As Long
Dim FilePath As String, FileName, FullName As String
Dim blnOpened As Boolean
Dim iCount As Integer


FilePath = "C:\Users\ozzy\Desktop\Makro2\" 'this is my path
FileName = "Book2.xlsm"
Call ToggleEvents(False)
Set wsStore = Workbooks(FileName).Sheets("Sheet1")


For iCount = 1 To 3
If iCount <> 2 Then
FullName = FilePath & "Book" & iCount & ".xlsx"
Set wbSource = Workbooks.Open(FullName)
Set wsSource = wbSource.Sheets("Sheet1") 'change destination sheet name here
LastRow = wsStore.Cells.Find(what:="*", after:=wsStore.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
wsStore.Cells(LastRow, "A").Value = wsSource.Cells(1, "B").Value
wsStore.Cells(LastRow, "B").Value = wsSource.Cells(4, "B").Value
wsStore.Cells(LastRow, "C").Value = wsSource.Cells(7, "B").Value
wsStore.Cells(LastRow, "D").Value = wsSource.Cells(7, "E").Value
wbSource.Close savechanges:=True
End If
Next iCount
Call ToggleEvents(True)
End Sub




Sub ToggleEvents(blnState As Boolean)
With Application
.DisplayAlerts = blnState
.EnableEvents = blnState
.ScreenUpdating = blnState
If blnState Then .CutCopyMode = False
If blnState Then .StatusBar = False
End With
End Sub


Function WbOpen(wbName As String) As Boolean
On Error Resume Next
WbOpen = Len(Workbooks(wbName).Name)
End Function

 

[yogiraj]

Hi ozan,
VBA ->Tools-> References
first tick on "Microsoft Scripting Runtime" references
change in code which is in bold.

This code definitely useful for u.
*************************************************************************
Option Explicit

Sub TransferData()
Dim wbStore, wbSource As Workbook, wsStore, wsSource As Worksheet, LastRow As Long
Dim FilePath As String, FileName, FullName As String
Dim blnOpened As Boolean
Dim iCount As Integer
Dim n as Long
Dim file1 As Folder
Dim fso As FileSystemObject
Set fso = CreateObject("scripting.filesystemobject")
Set file1 = fso.GetFolder("C:\Documents and Settings\yogesh\My Documents")
n = file1.Files.Count
FilePath = "C:\Documents and Settings\yogesh\My Documents" 'this is my path
FileName = "Book2.xlsm"
Call ToggleEvents(False)
Set wsStore = Workbooks(FileName).Sheets("Sheet1")

For iCount = 1 To n
If iCount <> 2 Then
FullName = FilePath & "Book" & iCount & ".xlsx"
Set wbSource = Workbooks.Open(FullName)
Set wsSource = wbSource.Sheets("Sheet1") 'change destination sheet name here
LastRow = wsStore.Cells.Find(what:="*", after:=wsStore.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
wsStore.Cells(LastRow, "A").Value = wsSource.Cells(1, "B").Value
wsStore.Cells(LastRow, "B").Value = wsSource.Cells(4, "B").Value
wsStore.Cells(LastRow, "C").Value = wsSource.Cells(7, "B").Value
wsStore.Cells(LastRow, "D").Value = wsSource.Cells(7, "E").Value
wbSource.Close savechanges:=True
End If
Next iCount
Call ToggleEvents(True)
End Sub


Sub ToggleEvents(blnState As Boolean)
With Application
.DisplayAlerts = blnState
.EnableEvents = blnState
.ScreenUpdating = blnState
If blnState Then .CutCopyMode = False
If blnState Then .StatusBar = False
End With
End Sub

Function WbOpen(wbName As String) As Boolean
On Error Resume Next
WbOpen = Len(Workbooks(wbName).Name)
End Function


*************************************************************************

Yogesh Dalimbkar
yogeshdalimbkar@gmail.com

 

[ozan efendi]

Hi, Yogiraj, thanks for the help and quick response.

I have still get the same error message. I ticked the tools-->reference->Microsoft Scripting Runtime

Then added the code but still getting the same error message (run-error '1004': C:\...........\Book4.xlsx could not be found.)

Do i have to clear some codes, becasue i only added the codes?

Regards,

Ozan.

 

[ozan efendi]

Yogiraj, hi i' m sorry, my bad

i only adjusted n = file1.Files.Count to n = file1.Files.Count -1

it works perfectly now,

Thank you so much,

Regards,

Ozan.