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.

 

[VoG]

You shouldn't need to activate them to work with them. But you can set a reference to a workbook then use that reference to activate it:

Sub GetFileName()
Dim fName As Variant, wb As Workbook
fName = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fName = False Then Exit Sub
Set wb = Workbooks.Open(fName)
wb.Activate
End Sub

 

[vinwin06]

Thanks for the reply. Actually if you see in vba codes it open up a file and copy the values and pasting it in another sheet. So what is the code we need to use it for that;

the code im referring is

Windows("2011-07 DS Dashboard1.xlsm").Activate

if i change this code with "wb.activate" it showing some error like subscript out of range.

can you help me on this.

 

[VoG]

Set a reference to the workbook that you open then refer to that reference later in the code.

 

[vinwin06]

Can you please provide me some example?

 

[jbeaucaire]

Can you confirm... your macro resides in the workbook Windows("2011-07 DS Dashboard1.xlsm") and the file you opened as a 'source' file was Windows("For July'11 DS Group Dashboard L1-2-3.xls"), and that second file is the one that needs to be variable.

 

[vinwin06]

yes you are correct.

 

[jbeaucaire]

When you run this macro, will you only be processing one sheet at a time? Your code appears to be using "mult-select" code, but you are pasting values to the same destination in your database, so it doesn't make sense to me that more than one set of data should be "incoming".

 

[jbeaucaire]

For a single file to be processed, try this:

Option Explicit

Sub ImportFile()
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")
        .Range("B6:B44").Copy
        wsDB.Range("B2").PasteSpecial Paste:=xlPasteValues, Transpose:=True
        
        .Range("C6:C44").Copy
        wsDB.Range("B20").PasteSpecial Paste:=xlPasteValues, Transpose:=True
        
        .Range("D6:D44").Copy
        wsDB.Range("B38").PasteSpecial Paste:=xlPasteValues, Transpose:=True
        
        .Range("E6:E44").Copy
        wsDB.Range("B74").PasteSpecial Paste:=xlPasteValues, Transpose:=True
        
        .Range("F6:F44").Copy
        wsDB.Range("B92").PasteSpecial Paste:=xlPasteValues, Transpose:=True
        
        .Range("G6:G44").Copy
        wsDB.Range("B110").PasteSpecial Paste:=xlPasteValues, Transpose:=True
    End With
        
    wbData.Close False      'close opened data book, no changes

End Sub

 

[vinwin06]

Thanks MVP. I have did some modification now its working for me. Thanks for your help.