Excel Macro expert please come ini'm new to macro. is it possible to write a macro which can read data from another excel file?

say macro is in file1.xls, i need to read data from file2.xls

many thanks.[o_o (3-31 9:58, Long long ago)] [ 传统版 | sForum ][登录后回复]1楼

方法很多，说个最简单易懂的比如说，如果你要把c盘file2里的一个数据表读进file1 spreadsheet1

如果没啥特别的要求，直接用
file1.Cells(i, j).Formula = "='C:\[file1.xls]spreadsheet1'!" & Cells(m, n).Address

(i, j)是file1数据的位置，(m, n)是file2相对应的数据位置[7-UP (3-31 14:39, Long long ago)] [ 传统版 | sForum ][登录后回复]2楼

(引用 7-UP:方法很多，说个最简单易懂的比如说，如果你要把c盘file2里的一个数据表读进file1 spreadsheet1 如果没啥特别的要求，直接用 file1.Cells...)不好意思，写错了 ：P把c盘file2 spreadsheet1里的一个数据表读进file1 spreadsheet1
在file1下写macro：
spreadsheet1.Cells(i, j).Formula = "='C:\[file2.xls]spreadsheet1'!" & Cells(m, n).Address

(i, j)是file1数据的位置，(m, n)是file2相对应的数据位置


很久很久没写，有点忘了，好像上述command需要file2是打开状态。。。所以可以加这么几行：
' open file2
ChDir "C:"
Workbooks.Open Filename:="C:\file2.xls"

'copy data
...
spreadsheet1.Cells(i, j).Formula = "='C:\[file2.xls]spreadsheet1'!" & Cells(m, n).Address
...

'close file2
Workbooks.Close Filename:="C:\file2.xls"[7-UP (3-31 14:55, Long long ago)] [ 传统版 | sForum ][登录后回复]3楼

前两天写的面试用的，run macro-> select file->processingPublic BoolOpenSuccess As Boolean


Private Sub start_Click()

Dim Value As String

Call OpenFile(StrFileName, BoolOpenSuccess)
Application.ScreenUpdating = False
If BoolOpenSuccess = False Then
Exit Sub
End If

'open file for input
Open StrFileName For Input As #5
Do While Not EOF(5)
On Error Resume Next
Line Input #5, strLine

'Get stepsize info
If BoolGetWidth = False Then
If InStr(1, strLine, "Pulse width:") > 0 Then
BoolGetWidth = True

If InStr(1, strLine, "ns") > 0 Then
'Pulse width
'Format: Pulse width:100ns
pos1 = InStr(1, strLine, ":")
pos2 = InStr(pos1, strLine, "s")
Value = Mid(strLine, pos1 + 1, pos2)
'store value in sheet
Worksheets("Result").Range("C10") = Value
Exit Do
End If

End If
End If

Loop

'close file
Close #5
End Sub

Sub OpenFile(strTemp, BoolOpenSuccess)

strTemp = Application.GetOpenFilename("All Files (*.*), *.*)")
If strTemp = False Then 'user chooses cancel
MsgBox "File Open Failed, Exit"
BoolOpenSuccess = False
Exit Sub
Else
BoolOpenSuccess = True
End If
End Sub

[nus_card (3-31 23:38, Long long ago)] [ 传统版 | sForum ][登录后回复]4楼

(引用 nus_card:前两天写的面试用的，run macro-> select file->processingPublic BoolOpenSuccess As Boolean Private Sub start_Click() Dim Value ...)如果你的file1位置固定，可以用以下的code 'get product and tech from user input
Location = Worksheets("control").Range("B34")
PartID = Worksheets("control").Range("G9")
Dim FilePath As String
FilePath = Location & "\" & PartID & "\"

If BoolManualXML = False Then
StrFileName = FilePath & ArrFileName(1)
Else

Call OpenFile(StrFileName, BoolOpenSuccess)
Application.ScreenUpdating = False
If BoolOpenSuccess = False Then
Exit Sub
End If
End If[nus_card (3-31 23:42, Long long ago)] [ 传统版 | sForum ][登录后回复]5楼

(引用 nus_card:如果你的file1位置固定，可以用以下的code 'get product and tech from user input Location = Worksheets("control").Range("B34") ...)学这个东西要先研究别人是怎样写的很容易上手的，加油
[nus_card (3-31 23:44, Long long ago)] [ 传统版 | sForum ][登录后回复]6楼

(引用 nus_card:如果你的file1位置固定，可以用以下的code 'get product and tech from user input Location = Worksheets("control").Range("B34") ...)上面的code用前要做些相应的修改:)[nus_card (3-31 23:45, Long long ago)] [ 传统版 | sForum ][登录后回复]7楼

(引用 nus_card:学这个东西要先研究别人是怎样写的很容易上手的，加油 )厉害~~那里去看别人写的呀？[九妹 (4-1 22:45, Long long ago)] [ 传统版 | sForum ][登录后回复]8楼

(引用 九妹:厉害~~那里去看别人写的呀？)我当时是实习，老板就把前辈的程序让我看Macro另外一个好处是可以record macro。这样你就能知道怎样写程序重复你刚才的动作，比如选中某个区域，改变背景颜色等[nus_card (4-2 13:40, Long long ago)] [ 传统版 | sForum ][登录后回复]9楼