[VBA] Importing one sheet from unspecified file in a folder to a newly created file, inside the same code.

MaxWork

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Hello People!

I am at the point in my job, where I need to make a macro, that would create a fresh excel file based on another excel file (like a template).
After that, the macro has to copy 1 specific sheet (For ex. "Summary") from an unspecified file in a different folder to an excel file, that was just created by the same macro!

(It is preferred, that the macro would execute using a command button)

I am very new to VBA, and was only able to make a few relatively easy macros in the past. This one however, proves to be more complex.

My code so far:

VBA Code:
Sub New_file_with_import()

Dim directory As String, fileName2 As String

'I was able to make the first part work, a fresh excel file is created based on a template and saved in a different folder'

Dim strTemplate As String: strTemplate = "Directory\Template File name"
Dim wb As Workbook
Set wb = Workbooks.Add(strTemplate)
    ActiveWorkbook.SaveAs Filename:="Directory\New file name.xlsx"
    ActiveWorkbook.Close

'The 2nd part doesn't work!'

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'This is where im trying to take the file from, from which ill be copying the sheet'

directory = "Directory"                          
fileName2 = Dir(directory & "*.xl??")
Workbooks.Open (directory & fileName2)

'How do i copy from an unspecified file? Basically, i need to copy from the only file in that folder'

Workbooks("?").Sheets("Summary").Copy _              
    after:=Workbooks("Directory\Freshly created file.xlsx").Sheets("Sheet1")                  

'Im trying to paste the sheet after the first sheet'

Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub



I know that the code is very messy as I'm trying different things to achieve the desired result.
Any help on this will be very appreciated!

Thanks!
 
Last edited by a moderator:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
Hi MaxWork and Welcome to the Board! You need to be more specific and provide a bit more detail. My understanding so far is that you want to copy your active wb to a new file with some unspecified name in some unspecified location. Then copy a sheet called "Summary" (again from some unspecified file again in some unspecified location) to the new file. Basic outline...
use copyfile to "new" file, open file to be copied "from", open new file, copy and paste "Summary" sheet, close "from" file without saving, close and save "new" file. If you can confirm this and provide the actual full path(s) of the "new" and "from" files, someone will likely be able to assist U. HTH. Dave
 

MaxWork

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Hi MaxWork and Welcome to the Board! You need to be more specific and provide a bit more detail...

Hey Dave!

Okay, I'll try to be more specific.
Let me make my request more structured:

1) I receive a daily report as an excel file. (So, the name varies, but always contains a word "report" in it). This file is the only file in a folder (Let's say "C:\Source Folder")
2) I need to extract just one sheet from this file (The name of the sheet is "Summary"), and put it in a new, freshly created file, that will always have a specific name. (Let's say "Transformed Report.xlsx") - The new file is saved in a different specified folder (Let's say C:\Destination Folder).
3) To achieve this, I made a macro, that creates this new file - "Transformed Report.xlsx" (This new file is based on another template file, like "Template.xlsx")
4) And now, i need to take the Sheet "Summary" from initially received file and paste it into - "Transformed Report.xlsx"

This is where I'm having trouble, I don't know how to Open and Copy from a file, which name I can't specify...

Here's the code with full paths:


VBA Code:
Sub Transformed_Report()

Dim directory As String, fileName2 As String

'I was able to make the first part work, a "Transformed Report.xlsx" is created based on a template and saved in "C:\Destination Folder"'

Dim strTemplate As String: strTemplate = "C:\Destination Folder\Template folder\Template.xlsx"
Dim wb As Workbook
Set wb = Workbooks.Add(strTemplate)
    ActiveWorkbook.SaveAs Filename:="C:\Destination Folder\Transformed Report.xlsx"
    ActiveWorkbook.Close

'The 2nd part doesn't work!'

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Here, I am trying to take initially received file from "C:\Source Folder\"'

directory = "C:\Source Folder\"                          
fileName2 = Dir(directory & "*.xl??")
Workbooks.Open (directory & fileName2)

'Now, i have to copy from it'

Workbooks("C:\Source Folder\directory & fileName2").Sheets("Summary").Copy _              
    after:=Workbooks("C:\Destination Folder\Transformed Report.xlsx").Sheets("Sheet1")                  

'Im trying to paste the sheet after the first sheet'

Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub

Hope I made myself more clear!
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
You can give this a trial. Not sure about the "report" being part of the file name somewhere. Dave
Code:
Sub test()
Dim OfsObj As Object, FilDir As Object, FilDir2 As Object
On Error GoTo QuitThis
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set OfsObj = CreateObject("Scripting.FilesystemObject")
'create new file
OfsObj.CopyFile "C:\Destination Folder\Template folder\Template.xlsx", _
"C:\Destination Folder\Transformed Report.xlsx"
'open new file
Set FilDir = OfsObj.getfile("C:\Destination Folder\Transformed Report.xlsx")
Workbooks.Open Filename:=FilDir
'open from file
Set FilDir2 = OfsObj.getfile("C:\Source Folder\" & "*" & "report" & "*" & ".xlsx")
Workbooks.Open Filename:=FilDir2
Workbooks(FilDir2.Name).Sheets("Summary").Copy _
Workbooks(FilDir.Name).Sheets(Workbooks(FilDir.Name).Sheets.Count)
Workbooks(FilDir.Name).Close SaveChanges:=True
Workbooks(FilDir2.Name).Close SaveChanges:=False

QuitThis:
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Error"
End If
Set OfsObj = Nothing
Set FilDir = Nothing
Set FilDir2 = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 

MaxWork

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows

ADVERTISEMENT

You can give this a trial. Not sure about the "report" being part of the file name somewhere. Dave

Hmm, it doesn't seem to work. Just gives me an error box, no debug screen either.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
Remove these 3 lines of code and see where the error is. Dave
Code:
On Error GoTo QuitThis
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 

MaxWork

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows

ADVERTISEMENT

Remove these 3 lines of code and see where the error is. Dave
Code:
On Error GoTo QuitThis
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Hmm, now it gives me an error in this line:

VBA Code:
'create new file
OfsObj.CopyFile "C:\Destination Folder\Template folder\Template.xlsx", _
"C:\Destination Folder\Transformed Report.xlsx"

It says that the file is not found?? But it is literally in there, i double checked the spelling and re-pasted directories.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
I wondered about your file name having a blank in the name. Trial adding this function and change of code.... or just remove the blank in the "Transformed Report" and see what happens. Dave
Code:
Public Function ValidFilePath(Arg As String) As String
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = "[\\/:\*\?""<>\|]"
    .Global = True
    ValidFilePath = .Replace(Arg, "_")
End With
Set RegEx = Nothing
End Function


Dim TempNameStr As String
TempNameStr = "Transformed Report"
OfsObj.CopyFile "C:\Destination Folder\Template folder\Template.xlsx", _
"C:\Destination Folder\" & ValidFilePath(TempNameStr) & ".xlsx"
 

MaxWork

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Hey Dave!

I was finally able to achieve the desired result.
I had to split the code into 2 separate macros.

Here is the code:
1st macro creates a new file, based on a template file and saves it under a new name;

VBA Code:
Sub Transforming_Report()

Dim strTemplate As String: strTemplate = "C:\Destination Folder\Template folder\Template"
Dim wb As Workbook
Set wb = Workbooks.Add(strTemplate)
    ActiveWorkbook.SaveAs fileName:="C:\Destination Folder\Transformed Report.xlsx"
    ActiveWorkbook.Close

End Sub

2nd macro, copies the sheet "Summary" from an unspecified file in a specified folder and pastes it into a newly created file;

VBA Code:
Sub Importing_Sheet()

Dim sourcepath As String
Dim sourcefile As String

Dim sourceworkbook As Variant
Dim newworkbook As Workbook

sourcepath = "C:\Source Folder\"
sourcefile = Dir(sourcepath & "*.*")

Do Until sourcefile = ""

Workbooks.Open sourcepath & sourcefile
sourceworkbook = sourcepath & sourcefile

Set newworkbook = Workbooks.Open("C:\Destination Folder\Transformed Report.xlsx")

Workbooks(sourcefile).Sheets("Summary").Copy after:=newworkbook.Sheets("Sheet1")
Workbooks(sourcefile).Close savechanges:=False
newworkbook.Save
newworkbook.Close

Set newworkbook = Nothing
    
    sourcefile = Dir()
Loop
    MsgBox ("Completed")

End Sub

I know this is not an ideal solution, but it works. Even though i wish my code was more clean.

Unfortunately, with my current level of VBA, your code is too complicated for me. I just don't understand your lines Dave.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,118
Messages
5,640,215
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top