VBA Codes for file open

[vinwin06]

Hi All,

I have a macro coding which help me to get the values from different workbook. i have pasted the code below;

Sub start()

Dim Str, Response, Checkxls, fname As String
Dim wkbDB As Workbook, fileToOpen As Variant, f As Integer
ThisWorkbook.Activate
wkbName = ActiveWorkbook.Name
Set wkbDB = ActiveWorkbook
openfile:
fileToOpen = Application.GetOpenFilename(, , "Select the latest downloaded 'Conceptos de Facturación MKA.CSV' file", , True)
If TypeName(fileToOpen) = "Boolean" Then Exit Sub
For f = 1 To UBound(fileToOpen)
If Right(fileToOpen(f), 4) <> ".xls" Then
Checkxls = MsgBox("The file being loaded is not a CSV file! Please try again", vbRetryCancel, "Error!")
If Checkxls = vbRetry Then
GoTo openfile
Else
Exit Sub
End If
Else
If fileToOpen(f) <> False Then

parts = Split(fileToOpen(f), "\")
strFilename = parts(UBound(parts))

Workbooks.Open fileToOpen(f)
ActiveWorkbook.RunAutoMacros xlAutoOpen

'----------

Sheets("A1 PO Non-compliance").Select
Range("B6").Select

Range("B6:B44").Select
Selection.Copy
Windows("2011-07 DS Dashboard1.xlsm").Activate
Sheets("A1 DS").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B3").Select

Range("B3:AN3").Select
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Windows("For July'11 DS Group Dashboard L1-2-3.xls").Activate
Sheets("A1 PO Non-compliance").Select
Range("c6").Select

Range("c6:c44").Select
Selection.Copy
Windows("2011-07 DS Dashboard1.xlsm").Activate
Range("B20").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B21").Select

Range("B21:AN21").Select
Selection.Copy
Range("B20").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Windows("For July'11 DS Group Dashboard L1-2-3.xls").Activate
Sheets("A1 PO Non-compliance").Select
Range("d6").Select

Range("d6:d44").Select
Selection.Copy
Windows("2011-07 DS Dashboard1.xlsm").Activate
Range("B38").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B39").Select

Range("B39:AN39").Select
Selection.Copy
Range("B38").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Windows("For July'11 DS Group Dashboard L1-2-3.xls").Activate
Sheets("A1 PO Non-compliance").Select
Range("e6").Select

Range("e6:e44").Select
Selection.Copy
Windows("2011-07 DS Dashboard1.xlsm").Activate
Range("B74").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B75").Select

Range("B75:AN75").Select
Selection.Copy
Range("B74").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Windows("For July'11 DS Group Dashboard L1-2-3.xls").Activate
Sheets("A1 PO Non-compliance").Select
Range("f6").Select

Range("f6:f44").Select
Selection.Copy
Windows("2011-07 DS Dashboard1.xlsm").Activate
Range("B92").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B93").Select

Range("B93:AN93").Select
Selection.Copy
Range("B92").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Windows("For July'11 DS Group Dashboard L1-2-3.xls").Activate
Sheets("A1 PO Non-compliance").Select
Range("g6").Select

Range("g6:g44").Select
Selection.Copy
Windows("2011-07 DS Dashboard1.xlsm").Activate
Range("B110").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B111").Select

Range("B111:AN111").Select
Selection.Copy
Range("B110").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'----------

End If
End If
Next f

End Sub


I have a clarification in only two codes mentioned below;

Windows("For July'11 DS Group Dashboard L1-2-3.xls").Activate
Windows("2011-07 DS Dashboard1.xlsm").Activate

The problem for me his the file name is different for each month. So what i need to use it for specifying file need to be activate. This is the macro for the file will open with browse option after that from the open file value has to taken and pasted in the macro running file.

Can anyone help me on this.

 

[vinwin06]

Hi MVP,

Can u please tell me how we can change this codes as vlookup to the particular sheet, so that its should be specific based on the field value. I have recorded something but i cant change the code as per the codes given by you. so can u help me on this.

 

[jbeaucaire]

Sure. Show me your code and point out the part you wish were variable.

 

[vinwin06]

Hi MVP,

Please find the codes below i have made changes in the code but still its going to opened sheet but i want that to go to the file having the macro.

Sub ImportFile1()
Dim wbData As Workbook
Dim wsDB As Worksheet, fName As String
Set wsDB = ThisWorkbook.Sheets("A1 DS") 'the destination sheet for data
TryAgain:
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = "C:\2010\Test\" 'default opening path
.AllowMultiSelect = False
.Filters.Add "All Files", "*.*" 'everything
.Filters.Add "Excel Files", "*.xls", 1 'default
.Show
If .SelectedItems.Count > 0 Then
fName = .SelectedItems(1)
Else
Exit Sub
End If
End With

If Right(fName, 4) <> ".xls" Then
If MsgBox("The file being loaded is not an XLS file! Please try again", vbRetryCancel, "Error!") = vbCancel Then
Exit Sub
Else
GoTo TryAgain
End If
End If
Set wbData = Workbooks.Open(fName)

With wbData.Sheets("A1 PO Non-compliance")
'
wsDB.Range("B2") = _
"=+VLOOKUP(R[-1]C,'[For August''11 DS Group Dashboard L1-2-3.xls]A1 PO Non-compliance'!C1:C7,2,0)"
Selection.Copy
Range("C2:AN2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

End Sub

 

[jbeaucaire]

Sure. Show me your code and point out the part you wish were variable.

You need to to point out the part of the code that is doing one thing and explain specifically what you want it to do instead.

 

[vinwin06]

I need to put a vlookup based on the value available in cell B2 to AN2 so the formula would be ;

=+VLOOKUP(B1,'[2011-07 DS Group Dashboard L1-2-3.xls]A1 PO Non-compliance'!$A:$G,2,0)

file we open based on the browse option is "2011-07 DS Group Dashboard L1-2-3.xls"

im doing the vlookup in sheet "A1 DS" macro placed workbook.

 

[jbeaucaire]

Maybe like so:

Option Explicit

Sub ImportFile1()
Dim wbData As Workbook
Dim wsDB As Worksheet, fName As String
'    Set wsDB = ThisWorkbook.Sheets("A1 DS")     'the destination sheet for data
TryAgain:
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = "C:\2010\Test\"      'default opening path
        .AllowMultiSelect = False
        .Filters.Add "All Files", "*.*"         'everything
        .Filters.Add "Excel Files", "*.xls", 1  'default
        .Show
        If .SelectedItems.Count > 0 Then
            fName = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
        
    If Right(fName, 4) <> ".xls" Then
        If MsgBox("The file being loaded is not an XLS file! Please try again", vbRetryCancel, "Error!") = vbCancel Then
            Exit Sub
        Else
            GoTo TryAgain
        End If
    End If
    
    Set wbData = Workbooks.Open(fName)
    fName = Right(fName, Len(fName) - InStrRev(fName, "\"))
    
    With wsDB.Range("B2:AN2")
        .FormulaR1C1 = "=VLOOKUP(R[-1]C,'[" & fName & "]A1 PO Non-compliance'!C1:C7,2,0)"
        .Value = .Value
    End With

End Sub

 

[vinwin06]

One more help i have cell like "B2B-India" but in my source file i have fields like "B2B-India Total".

So my lookup value is "B2B-India" but table array have value like "B2B-India Total".

So how i can make a vlookup formula to get the value?

Shall we write the vlookup code like this

.FormulaR1C1 = "=VLOOKUP([R[-1]C&" " &"Total"],'[" & fName & "]Z1 Cost to cut PO'!C1:C13,12,0)"

I need to change one i have highlighted above.

 

[jbeaucaire]

Your syntax looks correct if the value of R[-1]C = "B2B-India"

 

[vinwin06]

But in my source file it should be ""B2B-India Total" , i need to take the values from those it have total in the field name. how i will get that value.

Cell B2 = "B2B-India"
Source value = "B2B-India Total"

If the source having word "total", then how i can put the vlookup.

 

[jbeaucaire]

Your syntax above looked ok, are you now adding a space before TOTAL?

.FormulaR1C1 = "=VLOOKUP([R[-1]C&" " &" Total"],'[" & fName & "]Z1 Cost to cut PO'!C1:C13,12,0)"