TekillaSunrize
New Member
- Joined
- Sep 24, 2021
- Messages
- 12
- Office Version
- 2019
- 2016
- Platform
- Windows
Hello all,
I have this macro:
The 2 workbooks that are opened at the beginning of the scrip are "all-samples.csv" and "documentSearch.csv" the problem is that the files are found on the desktop with a different name ( "all-samples-xxxx-1234-xxxx.csv" or "documentSearch_xxxx.csv" having always the same only the a part of the name, so i always have to rename them in order to get found by the macro. Is there a way to change the macro in order to open this files based on partial name? Like adding the wildcard after "all-samples" and "documentSearch" so that i don't have to rename the files every time?
Also, if this is possibile, something has to be changed also in different parts of the macro, no? For example:
- Windows("documentSearch.csv").Activate - same problem with workbookname, it will have to activate any file starting with "documentSearch" found on desktop
- Kill (CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & "documentSearch.csv") - same thing
Very sorry if the questions is simple or stupid, I'm very new at this and still trying to figure out everything :D
Many thanks
I have this macro:
VBA Code:
Sub AndurilUploadSample(control As IRibbonControl)
'
' Anduril Macro
'
'
Application.ScreenUpdating = False
Workbooks.Open Filename:=(CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & "all-samples.csv")
Workbooks.Open Filename:=(CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & "documentSearch.csv")
Windows("all-samples.csv").Activate
Columns("G:G").Select
Selection.Copy
Windows("documentSearch.csv").Activate
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("C:D").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range("$A$1:$I$15138").AutoFilter Field:=4, Criteria1:=RGB(255 _
, 199, 206), Operator:=xlFilterCellColor
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.EntireRow.Delete
ActiveSheet.Range("$A$1:$I$14937").AutoFilter Field:=4
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("recalls_emea-sample-upload-template.csv").Activate
Range("F2").Select
ActiveSheet.Paste
Windows("documentSearch.csv").Activate
Range(Selection, Selection.End(xlUp)).Select
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("recalls_emea-sample-upload-template.csv").Activate
Range("G2").Select
ActiveSheet.Paste
Windows("documentSearch.csv").Activate
Range(Selection, Selection.End(xlUp)).Select
Range("H2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("recalls_emea-sample-upload-template.csv").Activate
Range("D2").Select
ActiveSheet.Paste
Windows("documentSearch.csv").Activate
Range(Selection, Selection.End(xlUp)).Select
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("recalls_emea-sample-upload-template.csv").Activate
Range("H2").Select
ActiveSheet.Paste
Columns("H:H").Select
Selection.Replace What:="T", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".*", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Columns("D:D").Select
Selection.Replace What:="asin research", Replacement:=" ", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="asin research", Replacement:=" ", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("H:H").Select
Application.CutCopyMode = False
Selection.NumberFormat = "yyyy/mm/dd hh:mm:ss"
Range("I2").Select
ActiveCell.FormulaR1C1 = "1"
Range("I2").Select
Selection.Copy
Columns("I:I").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveSheet.Paste
Range("B2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "IAS"
Range("B2").Select
Selection.Copy
Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveSheet.Paste
Range("A2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=MID(RC[6],FIND("" - "",RC[6],1)+3,10)"
Range("A2").Select
Selection.Copy
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows("all-samples.csv").Activate
ActiveWindow.Close
Windows("documentSearch.csv").Activate
ActiveWindow.Close SaveChanges:=False
Range("A1").Select
Kill (CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & "all-samples.csv")
Kill (CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & "documentSearch.csv")
MsgBox "Sample File Updated!"
End Sub
The 2 workbooks that are opened at the beginning of the scrip are "all-samples.csv" and "documentSearch.csv" the problem is that the files are found on the desktop with a different name ( "all-samples-xxxx-1234-xxxx.csv" or "documentSearch_xxxx.csv" having always the same only the a part of the name, so i always have to rename them in order to get found by the macro. Is there a way to change the macro in order to open this files based on partial name? Like adding the wildcard after "all-samples" and "documentSearch" so that i don't have to rename the files every time?
Also, if this is possibile, something has to be changed also in different parts of the macro, no? For example:
- Windows("documentSearch.csv").Activate - same problem with workbookname, it will have to activate any file starting with "documentSearch" found on desktop
- Kill (CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & "documentSearch.csv") - same thing
Very sorry if the questions is simple or stupid, I'm very new at this and still trying to figure out everything :D
Many thanks