Excel浏览文件夹并自动将数据输入默认模板 [英] Excel Browsing a folder and Automating Data Entry into a default template

问题描述

我一直在从各种来源读取网上提供的代码，并使用自学型编程进行调试以使其正常运行，但是我很难继续.

I been reading up codes available on the net from various sources and have debug with self-taught programming to make it work but I'm having difficulty proceeding on.

如您所见，它来自某个来源.浏览文件夹&读取文件与代码配合正常，我需要从此文件夹中复制值&将其粘贴到代码&中指定的默认模板中使用默认格式以及来自cell(O1)&的值保存文件(O11)在代码中分配.

As you can see, it comes from a source. Browsing a folder & reading the files works fine with the code, I need to copy values from this folder & paste it into the default template as assigned in the code & save the file with with a default format and alongside values from a cell(O1) & (O11) assign in the code.

如您所见，不会保存为xlsx，也不会保存为指定单元格中的值.

As you can see, is not saved as xlsx and neither is it saving with the values from cell specified.

接下来，自动将数据输入到分配的字段.仅前3个文件能够完全复制我想要的文件.其余的输入错误的数据，如下图所示.此外，我还需要将从文件夹中的文件读取的单元格N15:O83中的值复制到模板列AA&中.AB分别从第6行开始.

Next, automating data entry to assigned field. Only first 3 files are able to copy exactly what I want. The rest inputs wrong data, as shown in the image below. Additionally, I also need to copy values from cell N15:O83 read from files in folder, into template Column AA & AB starting from row 6 respectively.

在此先感谢您提供的任何帮助.

Thanks in advance for any assistance provided.

示例源文件正确的自动化自动化错误

宏代码

Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and             perform a set task on them
'SOURCE: www.TheSpreadsheetGuru.com

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim InstID As String
Dim InstDate As Date
Dim InstBR As String




'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
  .Title = "Select A Target Folder"
  .AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
    myPath = .SelectedItems(1) & "\"
End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

    'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"

  'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
'Set variable equal to opened workbook
  Set wb = Workbooks.Open(Filename:=myPath & myFile)

'Ensure Workbook has opened before moving on to next line of code
  DoEvents

'Input Code Here

  InstID = Range("O1")
  InstDate = Range("O11")
  InstBR = "Base Reading"

  wb.Worksheets(1).Range("B15:E83").Copy
  Workbooks.Add template:="C:\Users\PC1\Desktop\Daily data file\Inc\TestTemplate.xlsx"
  Sheets(ActiveSheet.Index + 1).Activate
  If Err.Number <> 0 Then Sheets(1).Activate
  Range("M6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Range("E6:F76") = InstID
    Range("K6:K76") = InstDate
    Range("J6") = InstBR

ChDir ("C:\Users\PC\Desktop\Daily data file\Inc\INC22001 - Copy\Test Save") ' Directory you need to save the file as xlsm
Filename = ("Test_Data_ ") & Range("O1").Value & ";" &     Range("O11").Value
ActiveWorkbook.SaveAs Filename:=Filename, FileFormat:=xlOpenXMLWorkbook

'Save and Close Workbook
  wb.Close SaveChanges:=True

'Ensure Workbook has closed before moving on to next line of code
  DoEvents

'Get next file name
  myFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Task Complete!"

ResetSettings:
  'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

推荐答案

这看起来有问题

Filename = ("Test_Data_ ") & Range("O1").Value & ";" &     Range("O11").Value
ActiveWorkbook.SaveAs Filename:=Filename, FileFormat:=xlOpenXMLWorkbook

您需要添加

& ".xlsx"

到第一行的末尾以正确形成工作簿文件名.

to the end of the top line to correctly form the workbook filename.

这篇关于Excel浏览文件夹并自动将数据输入默认模板的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！