×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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.

Students Click Here

Jobs

Help with Combine excel files

Help with Combine excel files

Help with Combine excel files

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

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