Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Help with Combine excel files

Help with Combine excel files

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

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()

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")
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.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.
' MsgBox("Modified (" + intcount.ToString + ") Excel files")
'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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close