Nbisgaard
07-23-2015, 03:37 AM
I have a macro to loop all the files in a folder, which works, and looks like this:
Sub ALLE_FILER_I_MAPPE()Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
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
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings
myExtension = "*.xls"
myFile = Dir(myPath & myExtension)
Do While myFile <> ""
Set wb = Workbooks.Open(Filename:=myPath & myFile)
wb.Worksheets(1).Range("D3").Formula = "=[data_til_dokumentation.xlsx]PI!$B$1"
'Save and Close Workbook
wb.Close SaveChanges:=True
myFile = Dir
Loop
MsgBox "Task Complete!"
ResetSettings:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
If i instead of
wb.Worksheets(1).Range("D3").Formula = "=[data_til_dokumentation.xlsx]PI!$B$1"
input:
wb.Worksheets(1).Range("N10").Formula = "=INDEX([data_til_dokumentation.xlsx]LS!$C$11:$C$18;MATCH(M10;[data_til_dokumentation.xlsx]LS!$B$11:$B$18;0)+0)"
I get an error Run-time error '1004':
I have checked that the formula i input actually do work when i copy paste it manually into the cell in question.
What am i doing wrong?
Sub ALLE_FILER_I_MAPPE()Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
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
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings
myExtension = "*.xls"
myFile = Dir(myPath & myExtension)
Do While myFile <> ""
Set wb = Workbooks.Open(Filename:=myPath & myFile)
wb.Worksheets(1).Range("D3").Formula = "=[data_til_dokumentation.xlsx]PI!$B$1"
'Save and Close Workbook
wb.Close SaveChanges:=True
myFile = Dir
Loop
MsgBox "Task Complete!"
ResetSettings:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
If i instead of
wb.Worksheets(1).Range("D3").Formula = "=[data_til_dokumentation.xlsx]PI!$B$1"
input:
wb.Worksheets(1).Range("N10").Formula = "=INDEX([data_til_dokumentation.xlsx]LS!$C$11:$C$18;MATCH(M10;[data_til_dokumentation.xlsx]LS!$B$11:$B$18;0)+0)"
I get an error Run-time error '1004':
I have checked that the formula i input actually do work when i copy paste it manually into the cell in question.
What am i doing wrong?