Setting a workbook as active without using work book name

Perksy_no1

Well-known Member
Joined
Oct 27, 2011
Messages
598
Office Version
  1. 365
Platform
  1. Windows
Hi there,

I've got a workbook which we change the name of weekly and i was wondering if vba would just look at it as an active workbook rather than me having to go into the VBA to change the name of the worbook every week for the VBA to run.

I export sheet from it then need to return to the actual workbook

Ie: 'test 19 10 11.xls' then moving to 'test 26 10 11.xls' the following week


Thanks
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
welcome to the board

The simple answer is yes, something is possible

please post your code, we can show you some examples of how best to change it
 

Perksy_no1

Well-known Member
Joined
Oct 27, 2011
Messages
598
Office Version
  1. 365
Platform
  1. Windows
ans = MsgBox("do you want to import NEW ALL ORD Warehouse Ian2", vbYesNo)
If ans = vbNo Then Exit Sub
Application.StatusBar = "processing"
Workbooks.Open(Filename:= _
"C:\Documents and Settings\mperk0\NEW ALL ORD Warehouse Ian2.xls"). _
RunAutoMacros Which:=xlAutoOpen
Range("A4:O8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("TE Back Order ReKeying Analysis RE 26.10.11.xls").Activate
Sheets("order data").Select
Range("A4").Select
ActiveSheet.Paste
Windows("NEW ALL ORD Warehouse Ian2.xls").Activate
ActiveWindow.Close savechanges = False

Call Inv_Warehouse

End Sub

The actual file which changes names is "TE Back Order Rekeying Analysis RE 26.10.11.xls"

Thanks
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
You could try something like this:

Code:
Sub import()
 
Dim fileSelected As Boolean, sourceAlreadyOpen As Boolean
Dim thisWkb As Workbook, sourceWkb As Workbook
Dim strSourceFilePath As String, strSourceFileName As String
 
fileSelected = True
 
' this section provides the ability to select a source workbook, using a normal file-open pop up
    
    Set thisWkb = ThisWorkbook
    
    ' build filter list for file open drop-down
    Dim strFilterList As String
    strFilterList = "Excel workbooks and templates, *.xls; *.xlt; *.xlsm; *.xlsx; *.xltm; *.xltx; *.xlsb"
    
    strSourceFilePath = Application.GetOpenFilename(strFilterList)
    If strSourceFilePath <> "False" Then
        strSourceFileName = GetFileName(strSourceFilePath)
        If isWorkbookOpen(strSourceFileName) = True Then
            Set sourceWkb = Workbooks(strSourceFileName)
            sourceAlreadyOpen = True
        Else
            Set sourceWkb = Workbooks.Open(fileName:=strSourceFilePath)
            sourceAlreadyOpen = False
        End If
    Else
        fileSelected = False
    End If
 
' this section processes if a file was selected
If fileSelected Then
    
    Application.StatusBar = "processing"
    
    Dim rngToCopy As Range
    With sourceWkb.Sheets("your sheet name here")
        Set rngToCopy = .Range(.Range("A4:O8"), .Range("A4:O8").End(xlDown))
    End With
    
    rngToCopy.Copy Destination:=thisWkb.Sheets("order data").Range("A4").Select
    
    If Not sourceAlreadyOpen Then sourceWkb.Close savechanges:=False
    Call Inv_Warehouse
End If
 
End Sub

Function GetFileName(filePath As String) As String
Dim StPathSep As String
Dim iFNLength As Integer, i As Integer

    StPathSep = Application.PathSeparator
    iFNLength = Len(filePath)
    
    For i = iFNLength To 1 Step -1
        If Mid(filePath, i, 1) = StPathSep Then Exit For
    Next i
    
    GetFileName = Right(filePath, iFNLength - i)
 
End Function
 
Function isWorkbookOpen(fileName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next
 
    Set Wkb = Workbooks(fileName)
 
    If Not Wkb Is Nothing Then
        isWorkbookOpen = True
    End If
 
End Function

This creates a pop-up that asks you to select the file. You can change the default location, so it always starts in a specific folder, for example

Note how I don't select or activate objects. Once I have declared them correctly, I refer to them directly, rather than select them, and then refer to the selection. It speeds up your code, simplifies things greatly, and reduces errors. With this in mind I would change the range being copied if possible, e.g. try using a named range?

Let me know if this doesn't work, I couldn't test it as I don't have access to the rest of your file
 

Perksy_no1

Well-known Member
Joined
Oct 27, 2011
Messages
598
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for this but it de bugs @ the highlighted bit below. The rest of it seems to be working though

' this section processes if a file was selected
If fileSelected Then

Application.StatusBar = "processing"

Dim rngToCopy As Range
With sourceWkb.Sheets("Sheet 1")
Set rngToCopy = .Range(.Range("A4:O8"), .Range("A4:O8").End(xlDown))
End With

rngToCopy.Copy Destination:=thisWkb.Sheets("order data").Range("A4").Select

If Not sourceAlreadyOpen Then sourceWkb.Close savechanges:=False
Call Inv_Warehouse
End If

End Sub
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
sorry, left some code in that shouldn't be there... no need for the select...

try
rngToCopy.Copy Destination:=thisWkb.Sheets("order data").Range("A4")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,321
Messages
5,600,950
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top