Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with Combine excel files

Status
Not open for further replies.

Xsi

Programmer
May 29, 2015
121
0
0
SE
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top