Progress has been made, but now I'm stuck...

[donthate918]

Okay, I have these codes that do exactly what I want when I run them manually:

1. Merges all the reports into one workbook

Sub MergeAllWorkbooks()
    
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim NRow As Long
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    
    ' Create a new workbook and set a variable to the first sheet.
    ' I want to make this the current workbook,sheet 1
    Set SummarySheet = ThisWorkbook.Worksheets("DailyInspectionReports")
    
    ' Modify this folder path to point to the files you want to use.
    FolderPath = "S:\Quality Control\Reports\Inspection\2017\"
    
    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 2
    
    ' Call Dir the first time, pointing it to all Excel files in the folder path.
    FileName = Dir(FolderPath & "*.xl*")
    
    ' Loop until Dir returns an empty string.
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set WorkBk = Workbooks.Open(FolderPath & FileName)
        
        ' Set the cell in column A to be the file name.
        ' Not using this feature.
        ' SummarySheet.Range("A" & NRow).Value = FileName
        
        
        ' Set the source range to be cell through cell or range.
        ' Modify this range for your workbooks.
        ' It can span multiple rows.
        lastRow = WorkBk.Worksheets(1).Cells(Rows.Count, "C").End(xlUp).Row
        Set SourceRange = WorkBk.Worksheets(1).Range("A9:J" & lastRow)
        
        ' Set the destination range to start at column and
        ' be the same size as the source range.
        ' I also want the destination sheet to stay the same size and formatting.
        Set DestRange = ThisWorkbook.Worksheets("DailyInspectionReports").Range("A" & NRow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)
           
        ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value
        
        ' Increase NRow so that we know where to copy data next.
        NRow = NRow + DestRange.Rows.Count
        
        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False
        
        ' Use Dir to get the next file name.
        FileName = Dir()
    Loop
    
    ' Call AutoFit on the destination sheet so that all
    ' data is readable.
    ThisWorkbook.Worksheets(1).Columns.AutoFit
End Sub

2. Saves the file to the location I want.

Sub SaveFileAs()

 Dim strPath As String
 Dim strFolderPath As String

    strFolderPath = "S:\Quality Control\Reports\Daily Batch Files\Inspection\"

    strPath = strFolderPath & "Daily Inspection Report Summary" & "-" & Format(Now, "mm-dd-yyyy-hh_mm")
    
    ActiveWorkbook.SaveAs FileName:=strPath
    
End Sub

But when I try to run this .vbs it throws an error on the last line of the "SaveFileAs" line of code

dim eApp
set eApp = GetObject("S:\Quality Control\Reports\Daily Inspection Report Summary.xlsm")
eApp.Application.Run "'Daily Inspection Report Summary.xlsm'!MergeAllWorkbooks"
eApp.Application.Run "'Daily Inspection Report Summary.xlsm'!SaveFileAs"
set eApp = nothing

I can run the .vbs in a macro in a blank workbook and it works just fine.

I want to set a task in scheduler to run this for me.

Any ideas? Also, any help is greatly appreciated.

 

[JLGWhiz]

In your file name for the SaveAs, I think you might want:

Format(Now, "mm-dd-yyyy-hh_[COLOR=#B22222]nn[/COLOR]")

It probably doen't know what to do with the mm if it exceeds a value of 12.

 

[donthate918]

Still no dice. Hangs when running .vbs script at the
"ActiveWorkbook.SaveAs FileName:=strPath"
line in the last macro.

 

[Joe4]

Duplicate: https://www.mrexcel.com/forum/excel-questions/1026220-fix-code-import-data.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.

I have closed your other thread. Please do not start any new threads on this same question.

 

[donthate918]

Okay I've narrowed it down, and it is the .vbs script.

 

[JLGWhiz]

The code should work technically, but if your file path has misspelled words or the path is otherwise invalid then the SaveAs will not process. Are you getting an error message, and if so, what is it?

Also, I notice that there is no file extension included in your file name. It still should process as a .xlsx default, but it is good practice to include the file extension in versions after Excel2007..

 

[donthate918]

Fixed....

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("S:\Quality Control\Reports\Daily Inspection Report Summary.xlsm")
objExcel.Application.Visible = True
objExcel.Application.Run "'Daily Inspection Report Summary.xlsm'!MergeAllWorkbooks"
Set objWorkbook = objExcel.Workbooks.Close
WScript.Quit