workbooks.open question

[vassili]

when this line runs, it opens the file in a new workbook. how do i get it so that it opens in the active workbook (where the macro is running) and in sheet1?

[vosmerkin]

Every file is a workbook. It's impossimble to open another file (with workbook.open command) in the active workbook or sheet. Please correct me somebody if I'm wrong

[vassili]

so how do i import another excel file into my current workbook without using the import data wizard?

everytime i use that thing, the form i try to import gets messed up. and some forms have multiple tables and i can only choose one with the wizard.

right now, i just wrote a simple vba code to open, then copy the newly opened file and then paste it into a new sheet "ala"

If Fname = "" Or Dir("c:\..........BOM" & Fname & ".xls") = "" Then
MsgBox "BOM # " & Fname & " not found !", vbExclamation

Exit Sub

Else
Workbooks.Open Filename:="c:\...........BOM" & Fname & ".xls"

End If

Windows("BOM" & Fname & ".xls").Activate
Cells.Select
Range("BO186").Activate
Selection.Copy
Windows("current.xls").Activate
Sheets.Add
ActiveSheet.Name = "Current BOM"
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("BOM" & Fname & ".xls").Activate
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Sheets("ITECH").Select

i also have a problem with sheet naming conflicts. if you run it more than once, it'll try to create a new sheet and name it current BOM, which already exists.

i want it so i overwrites the original sheet and keep the same sheet name. how can i resolve this?

[lucas]

when this line runs, it opens the file in a new workbook. how do i get it so that it opens in the active workbook (where the macro is running) and in sheet1?
You have correctly answered your own question:


Every file is a workbook. It's impossimble to open another file (with workbook.open command) in the active workbook or sheet. Please correct me somebody if I'm wrong


You don't say whether your import code is working correctly but for this problem:

i also have a problem with sheet naming conflicts. if you run it more than once, it'll try to create a new sheet and name it current BOM, which already exists.

i want it so i overwrites the original sheet and keep the same sheet name. how can i resolve this?
Try using this before you save it:
application.displayalerts = False

[mdmackillop]

Option Explicit
Sub getcopy()
Dim Source As Workbook
Dim Target As Workbook
Dim shName As String
Dim chk As Worksheet
Set Target = Workbooks("Book1.xls")
Set Source = Workbooks("Book2.xls")
shName = Source.Sheets(1).Name
On Error Resume Next
Set chk = Target.Sheets(shName)
If chk Is Nothing Then
Source.Sheets(1).Copy Before:=Target.Sheets(1)
Else
Application.DisplayAlerts = False
chk.Delete
Application.DisplayAlerts = True
Source.Sheets(1).Copy Before:=Target.Sheets(1)
End If
End Sub

[vassili]

Option Explicit
Sub getcopy()
Dim Source As Workbook
Dim Target As Workbook
Dim shName As String
Dim chk As Worksheet
Set Target = Workbooks("Book1.xls")
Set Source = Workbooks("Book2.xls")
shName = Source.Sheets(1).Name
On Error Resume Next
Set chk = Target.Sheets(shName)
If chk Is Nothing Then
Source.Sheets(1).Copy Before:=Target.Sheets(1)
Else
Application.DisplayAlerts = False
chk.Delete
Application.DisplayAlerts = True
Source.Sheets(1).Copy Before:=Target.Sheets(1)
End If
End Sub



thanks man, i'll try it out.