File Retrieval - Macro won't work with File name of numbers over 1,048,576 1MB

[Brett.Austin]

Hi all


I am stumped! "BatchNumber" has been set, in my sheet if I use a batch number such as "765845" (6 digits), its all ok, but larger numbers don't work.


I have played "The Price is Right Showcase!" to narrow down the maximum number the code allows: and surprise the magic number is "1,048,576" (1MB!)


Can someone explain why, and help me to allow larger numbers and/or alpha-numeric combos.


This macro retrieves a group of files saved in directories on the network drive, and copies them to a designated directory.


Files might be "765898.pdf" or "IWREFL-12000032.pdf"


Thanks

[pre]

Sub MultiBatchRetrieval()

Dim BatchNumber As Variant
Dim BatchURL As Variant
Dim StartRow As Variant

Dim i As Variant

Dim eScriptObject As Object
Dim fso
Dim file As String, sfol As String, dfol As String

ActiveSheet.Calculate

StartRow = Rows(Range("APScanMultiBatchNo_Start"))

For i = 10 To 42

BatchNumber = Cells(i, 11)
BatchURL = Cells(i, 16)

file = BatchNumber & ".pdf"
sfol = BatchURL
dfol = Range("MultiBatchFolder")

Set eScriptObject = CreateObject("Scripting.FileSystemObject")

If eScriptObject.FileExists(BatchURL & BatchNumber & ".pdf") Then

Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(sfol & file) Then
Cells(i, 13) = "Not Found"
ElseIf Not fso.FileExists(dfol & file) Then
fso.CopyFile (sfol & file), dfol, True
Cells(i, 13) = "Copied"
Else
Cells(i, 13) = "Copied"
End If

End If
Next i

End Sub

[/pre]

 

[Hui]

Brett


Try formatting the cells with the Batch Numbers as 0

so that the number s show up as 1048576

Not as 1,048,576


or change the line

BatchNumber = Cells(i, 11)

to

BatchNumber = Cells(i, 11).value2

I think the ,'s maybe throwing it out

 

[Brett.Austin]

Hi Hui


That wasn't the issue sorry.


I have resolved. I had code in there that didnt belong.


StartRow = Rows(Range("APScanMultiBatchNo_Start"))


Deleted that and it works.


I already told it where the start row is for the BatchNumber = Cells(i, 11) and i being 10 to 42