Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting & Importing with macros
It's possible to write a macro that copy a value that is locate always
in the same position but in different files, this files follow a nomeclature with logic. And after that paste it in other file. Example: In "file1.xls" I want to copy the value in cell "B6" and copy in the file "all.xls" in cell "B1" In "file2.xls" I want to copy the value in cell "B6" and copy in the file "all.xls" in cell "B2" In "file3.xls"... And then towards... Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting & Importing with macros
Hi there
One way to do it would be to use the file system object. This method is handy if you don't want to hard code all the file names you want to check. It works by checking all the files in the given folder (C: \Test) in this example. It will loop through all the files and check for the excel file extension when it files the files it will open them read the value in B6 and then close the file and write the stored value into the next available cell in column B of the workbook that holds the code. Sub GetCellB6FromEachFile() Dim intRow As Integer Dim objFSO As Object Dim objFile As Object Dim objFolder As Object Dim vntB6Value As Variant Dim wkbk As Workbook Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFolder = objFSO.getfolder("C:\Test") intRow = 1 For Each objFile In objFolder.Files If Right(objFile.Name, 4) = ".xls" Then Set wkbk = Workbooks.Open(objFile) With wkbk vntB6Value = Sheets(1).[B6].Value wkbk.Saved = True .Close Set wkbk = Nothing End With ThisWorkbook.Sheets(1).Cells(intRow, 2).Value = _ vntB6Value intRow = intRow + 1 End If Next Set objFSO = Nothing Set objFolder = Nothing Set objFile = Nothing End Sub I hope this helps Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting & Importing with macros
Option Explicit
Sub getdata() Dim fn As String Dim wb As Workbook Dim index As Long Const filepath As String = "H:\excel\test\" fn = Dir(filepath & "*.xls") Do Until fn = "" index = Mid(fn, 5, Len(fn) - 8) Set wb = Workbooks.Open(filepath & fn) ThisWorkbook.ActiveSheet.Cells(index, "B") = wb.ActiveSheet.Range("B6").Value wb.Close False fn = Dir() Loop End Sub "CAUĂ" wrote: It's possible to write a macro that copy a value that is locate always in the same position but in different files, this files follow a nomeclature with logic. And after that paste it in other file. Example: In "file1.xls" I want to copy the value in cell "B6" and copy in the file "all.xls" in cell "B1" In "file2.xls" I want to copy the value in cell "B6" and copy in the file "all.xls" in cell "B2" In "file3.xls"... And then towards... Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting & Importing with macros
my files were BOOK1.xls, BOOK2.xls and so on
so the index started at the 5th character and the number of characters was the len() of the file name less 8 characters (BOOK + .XLS) yours will be the same if th efiles all start with the word 'file' "Patrick Molloy" wrote: Option Explicit Sub getdata() Dim fn As String Dim wb As Workbook Dim index As Long Const filepath As String = "H:\excel\test\" fn = Dir(filepath & "*.xls") Do Until fn = "" index = Mid(fn, 5, Len(fn) - 8) Set wb = Workbooks.Open(filepath & fn) ThisWorkbook.ActiveSheet.Cells(index, "B") = wb.ActiveSheet.Range("B6").Value wb.Close False fn = Dir() Loop End Sub "CAUĂ" wrote: It's possible to write a macro that copy a value that is locate always in the same position but in different files, this files follow a nomeclature with logic. And after that paste it in other file. Example: In "file1.xls" I want to copy the value in cell "B6" and copy in the file "all.xls" in cell "B1" In "file2.xls" I want to copy the value in cell "B6" and copy in the file "all.xls" in cell "B2" In "file3.xls"... And then towards... Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing and Exporting Data | Excel Discussion (Misc queries) | |||
Exporting/Importing Data | Excel Discussion (Misc queries) | |||
Importing and Exporting Data to Web | Excel Discussion (Misc queries) | |||
Data Importing and Exporting | Excel Programming | |||
Exporting & Importing Macros | Excel Programming |