Dear All,
I have multiple csv files in a local directory in my computer. Using following code, I am able to open each csv file and paste in master excel sheet. However, when I change location from my local directory to ftp server, I am getting this message: Run-time error '52' -Bad file name or number
In short, instead of having "folderpath" in my computer, it has to be in ftp server. THanks in advance for help
Code....
Private Sub CommandButton11_Click()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range
' Downloadig files from a location in the laptop
FolderPath = "C:\Users\hahmed\Box Sync\Success stories\October2016"
' Downloadig files from a ftp server
' FolderPath = "ftp://10.128.158.152"
' To start copying from row 2
NRow = 2
' Call Dir the first time, pointing it to all Excel files in the folder path.
FileName = Dir(FolderPath & "*.csv*")
' Loop until Dir returns an empty string.
Do While FileName <> ""
' Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName)
' On Error Resume Next
ActiveSheet.Range("A1:bi1").Select
Selection.Copy
Windows("Success_storiesMsol_Oct2016_blankoCSV.xlsm").Activate
Range("A" & NRow).Select
Selection.PasteSpecial
Set WorkBk = Workbooks.Open(FolderPath & FileName)
ActiveSheet.Pictures.Copy
On Error Resume Next
With Workbooks("Success_storiesMsol_Oct2016_blanko.xlsm").Sheets("Jan 2015")
.Parent.Activate
.Activate
.Range("BF" & NRow).Select
.Paste
On Error Resume Next
End With
On Error Resume Next
Application.CutCopyMode = False
' Increase NRow so that we know where to copy data next.
NRow = NRow + 1
' Close the source workbook without saving changes.
WorkBk.Close savechanges:=False
' Use Dir to get the next file name.
FileName = Dir()
Loop
For Each Cell In Range("Ab1", Range("Ab6500").End(xlUp))
If (Left(Cell, 4) = "SESA") Then
Cell.Value = Right(Cell, Len(Cell) - 4)
End If
Next Cell
MsgBox "All files from the folder have been imported"
' Call AutoFit on the destination sheet so that all
' data is readable.
' SummarySheet.Columns.AutoFit
End Sub
I have multiple csv files in a local directory in my computer. Using following code, I am able to open each csv file and paste in master excel sheet. However, when I change location from my local directory to ftp server, I am getting this message: Run-time error '52' -Bad file name or number
In short, instead of having "folderpath" in my computer, it has to be in ftp server. THanks in advance for help
Code....
Private Sub CommandButton11_Click()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range
' Downloadig files from a location in the laptop
FolderPath = "C:\Users\hahmed\Box Sync\Success stories\October2016"
' Downloadig files from a ftp server
' FolderPath = "ftp://10.128.158.152"
' To start copying from row 2
NRow = 2
' Call Dir the first time, pointing it to all Excel files in the folder path.
FileName = Dir(FolderPath & "*.csv*")
' Loop until Dir returns an empty string.
Do While FileName <> ""
' Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName)
' On Error Resume Next
ActiveSheet.Range("A1:bi1").Select
Selection.Copy
Windows("Success_storiesMsol_Oct2016_blankoCSV.xlsm").Activate
Range("A" & NRow).Select
Selection.PasteSpecial
Set WorkBk = Workbooks.Open(FolderPath & FileName)
ActiveSheet.Pictures.Copy
On Error Resume Next
With Workbooks("Success_storiesMsol_Oct2016_blanko.xlsm").Sheets("Jan 2015")
.Parent.Activate
.Activate
.Range("BF" & NRow).Select
.Paste
On Error Resume Next
End With
On Error Resume Next
Application.CutCopyMode = False
' Increase NRow so that we know where to copy data next.
NRow = NRow + 1
' Close the source workbook without saving changes.
WorkBk.Close savechanges:=False
' Use Dir to get the next file name.
FileName = Dir()
Loop
For Each Cell In Range("Ab1", Range("Ab6500").End(xlUp))
If (Left(Cell, 4) = "SESA") Then
Cell.Value = Right(Cell, Len(Cell) - 4)
End If
Next Cell
MsgBox "All files from the folder have been imported"
' Call AutoFit on the destination sheet so that all
' data is readable.
' SummarySheet.Columns.AutoFit
End Sub