2

I have 2 different type of files. 1 is ".tsv" 2 is ".xlsx".

I want my macro (.xlsm) file to be in same folder where 2 different files (.tsv and .xlsx) are placed. I usually download them from some tools and normally they are placed in my default "Download" folder.

Problem 1: I want my macro file to read both format and copy paste data into single excel file. I am done with this problem. Code optimization is required.

Problem 2: I am currently assigning manual path to that folder and want macro to pick that folder path so that it can copy 2 extension files and proceed. (Tried : ActiveWorkbook.Path, didn't work). 

Dim FPath As String, filename As String, FileExt1 As String, FileExt2 As 
String

Sub GetSheets()
FPath = "C:\Users\dinekuma\Desktop\Dinesh KT\Macro New UI\"
FileExt1 = "*.tsv"
FileExt2 = "*.xlsx"

'ActiveWorkbook.Path
'"C:\Users\dinekuma\Desktop\Dinesh KT\Macro New UI\"

filename = Dir(FPath & FileExt1)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

  Do While filename <> ""
  Workbooks.Open filename:=FPath & filename, ReadOnly:=True
     For Each sheet In ActiveWorkbook.Sheets
     sheet.Copy After:=ThisWorkbook.Sheets(1)
    ActiveSheet.Name = Split(filename, ".")(0)
       Next sheet
     Workbooks(filename).Close
     filename = Dir()

  Loop

  filename = Dir(FPath & FileExt2)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

  Do While filename <> ""
  Workbooks.Open filename:=FPath & filename, ReadOnly:=True
     For Each sheet In ActiveWorkbook.Sheets
 sheet.Copy After:=ThisWorkbook.Sheets(1)
ActiveSheet.Name = Split(filename, ".")(0)
   Next sheet
 Workbooks(filename).Close
 filename = Dir()

  Loop
  MsgBox ("Import Successful!")

End Sub

Help in code optimization and automatic path pick by active macro file.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
dFialok
  • 21
  • 3
  • For the problem 2 try to read [this](https://stackoverflow.com/a/2814014/7690982). And are the files located locally or on the network? – danieltakeshi Oct 11 '17 at 13:47
  • 1
    For problem 2 @danieltakeshi already provided a good link. Concerning the code optimization: Please read [this on meta](https://meta.stackoverflow.com/questions/261841/can-i-post-questions-about-optimizing-code-on-stack-overflow) - It's better to go to Code Review with problem 1, since the actual problem is already solved. – Rik Sportel Oct 11 '17 at 13:54
  • Files are placed locally. – dFialok Oct 11 '17 at 13:55
  • I have found a temp work around for this FPath = 'ActiveWorkbook.Path & "\" and is working fine. – dFialok Oct 11 '17 at 13:56
  • Can i use some conditional statement to check FileExt as ".xlsx or .tsv" rather than running loop for both formats – dFialok Oct 11 '17 at 13:57
  • But before posting on Code Review, please read [this](https://codereview.meta.stackexchange.com/questions/5777/a-guide-to-code-review-for-stack-overflow-users) – danieltakeshi Oct 11 '17 at 14:03
  • @dFialok You can use [Like operator](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/like-operator) with wildcard, e.g.: `If FileExt Like "*.xlsx" Then 'do something'` – danieltakeshi Oct 11 '17 at 14:06
  • thanks @danieltakeshi. will look forward to code review and will try Like Operator. will update you soon – dFialok Oct 11 '17 at 14:16

0 Answers0