Hello all Tek-Tips Members,
I have made a vba macro that combines xls files into one
Sub CombineFiles()
Dim Path As String
Dim fileName As String
Dim Wkb As Workbook
Dim ws As Worksheet
ActiveSheet.Name = "Volvo_Statistik"
intResult = Application.FileDialog(msoFileDialogFolderPicker).Show
If intResult = 0 Then
MsgBox "User pressed cancel macro will stop!"
Exit Sub
Else
strDocPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
End If
Application.EnableEvents = False
Application.ScreenUpdating = False
fileName = Dir(strDocPath & "\*.xls", vbNormal)
Do Until fileName = ""
Set Wkb = Workbooks.Open(fileName:=strDocPath & "\" & fileName)
For Each ws In Wkb.Worksheets
Application.DisplayAlerts = False
wbname = Replace(fileName, ".xls", "")
'MsgBox WBname
ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next ws
ActiveSheet.Name = (wbname)
Wkb.Close False
fileName = Dir()
Loop
Worksheets("Volvo_Statistik").Move Before:=Worksheets(1)
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Could someone help me to convert this script into vb or give me tutorials,
I googled very much and I found some but not enough.
I got this code so far
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
FolderBrowserDialog1.SelectedPath = My.Computer.FileSystem.SpecialDirectories.MyDocuments
FolderBrowserDialog1.ShowNewFolderButton = True 'folderdialog button
If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
Dim mySelFile As String = FolderBrowserDialog1.SelectedPath
Dim intcount As Integer = Nothing
Tb_FilePath.Text = mySelFile
If Not IO.Directory.GetFiles(mySelFile, "*.xls").Any() Then
MsgBox("there are no xls files here")
Application.Restart()
End If
For Each filename As String In IO.Directory.GetFiles(mySelFile, "*.xls") 'I define the type of all files the loop go through
excelApp.Workbooks.Open(filename)
excelApp.Visible = True
Dim Path As String
Dim Wkb As Excel.Workbook
Dim ws As Excel.Worksheet
excelApp.ActiveSheet.Name = "Volvo_statistik"
' Create a new instance of Excel and make it visible.
Next
' MsgBox("Modified (" + intcount.ToString + ") Excel files")
Else
'if the user has not selected a folder, it is a warning
MsgBox("No Folder selected", MsgBoxStyle.Exclamation, "No selected folders")
End If
End Sub
I have made a vba macro that combines xls files into one
Sub CombineFiles()
Dim Path As String
Dim fileName As String
Dim Wkb As Workbook
Dim ws As Worksheet
ActiveSheet.Name = "Volvo_Statistik"
intResult = Application.FileDialog(msoFileDialogFolderPicker).Show
If intResult = 0 Then
MsgBox "User pressed cancel macro will stop!"
Exit Sub
Else
strDocPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
End If
Application.EnableEvents = False
Application.ScreenUpdating = False
fileName = Dir(strDocPath & "\*.xls", vbNormal)
Do Until fileName = ""
Set Wkb = Workbooks.Open(fileName:=strDocPath & "\" & fileName)
For Each ws In Wkb.Worksheets
Application.DisplayAlerts = False
wbname = Replace(fileName, ".xls", "")
'MsgBox WBname
ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next ws
ActiveSheet.Name = (wbname)
Wkb.Close False
fileName = Dir()
Loop
Worksheets("Volvo_Statistik").Move Before:=Worksheets(1)
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Could someone help me to convert this script into vb or give me tutorials,
I googled very much and I found some but not enough.
I got this code so far
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
FolderBrowserDialog1.SelectedPath = My.Computer.FileSystem.SpecialDirectories.MyDocuments
FolderBrowserDialog1.ShowNewFolderButton = True 'folderdialog button
If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
Dim mySelFile As String = FolderBrowserDialog1.SelectedPath
Dim intcount As Integer = Nothing
Tb_FilePath.Text = mySelFile
If Not IO.Directory.GetFiles(mySelFile, "*.xls").Any() Then
MsgBox("there are no xls files here")
Application.Restart()
End If
For Each filename As String In IO.Directory.GetFiles(mySelFile, "*.xls") 'I define the type of all files the loop go through
excelApp.Workbooks.Open(filename)
excelApp.Visible = True
Dim Path As String
Dim Wkb As Excel.Workbook
Dim ws As Excel.Worksheet
excelApp.ActiveSheet.Name = "Volvo_statistik"
' Create a new instance of Excel and make it visible.
Next
' MsgBox("Modified (" + intcount.ToString + ") Excel files")
Else
'if the user has not selected a folder, it is a warning
MsgBox("No Folder selected", MsgBoxStyle.Exclamation, "No selected folders")
End If
End Sub