PDA

View Full Version : Links updates and split workbook CSV



farrukh
08-13-2011, 08:41 PM
Hi All,

I have a code that perfectly works only little amendment required.

1. Hard coded the excel master.xlsm (path in the folder) Code do not browse to search.

2. when it creates csv in the result folder (create the name of csv like sheet1,sheet2,sheet3 and so no) i need the name of worksheet tab as a .csv file name suppose worksheet tab name is test then create csv in the result folder like test.csv.

The code is below

Sub test()
Dim lngcount As Integer, x As Integer, ipath As String
With Application: .ScreenUpdating = False: .AskToUpdateLinks = False
With .FileDialog(msoFileDialogFilePicker): .AllowMultiSelect = False
If .Show = -1 Then
ipath = ThisWorkbook.Path & "\" & "result": Dir (ipath)
For lngcount = 1 To .SelectedItems.Count
Workbooks.Open (.SelectedItems(lngcount)): ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
For Each sh In ActiveWorkbook.Sheets
x = x + 1: sh.Copy: ActiveWorkbook.SaveAs Filename:=ipath & "\" & x & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Workbooks(x & ".csv").Close Savechanges = True
Next
Next
End If
End With .ScreenUpdating = True:
End With
End Sub


Thanks
haris