Solved: creating new workbook/searching for wkbook

[Anomandaris]

Hi guys, got another one for ya

This is what I’m trying to do.

Click a button(macro) – it opens the browse window so I can search for a file, once I select an Excel file – macro automatically opens to a worksheet called ‘Roll’, then it copies this worksheet to a new Workbook (at this point it will chnage the arrangement of the data in accordance to a macro which i already have, I'll just paste it into the macro you prepare here)
and the it SAVES the file. The Workbook that was searched for and opened, AND the workbook that was created and saved should both be CLOSED. The initial workbook with the macro may remain open.

Any ideas……?
Thanks

[Bob Phillips]

Dim Filename As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook

Filename = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If Filename <> False Then

Set wbSource = Workbooks.Open(Filename)
wbSource.Worksheets("Roll").Copy
Set wbTarget = ActiveWorkbook

'your code
Filename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Filename <> False Then

wbTarget.SaveAs Filename

wbTarget.Close
wbSource.Close SaveChanges:=False
End If
End If

[Bob Phillips]

Dim Filename As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook

Filename = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If Filename <> False Then

Set wbSource = Workbooks.Open(Filename)
wbSource.Worksheets("Roll").Copy
Set wbTarget = ActiveWorkbook

'your code
Filename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Filename <> False Then

wbTarget.SaveAs Filename

wbTarget.Close
wbSource.Close SaveChanges:=False
End If
End If

[Anomandaris]

hey xld thanks,

but when i select the excel file, it doesnt save the file or do anything else, just stops..

[Bob Phillips]

You should get one dialog to open the file, and another to save the new file. Do you get both?

[Anomandaris]

No, I get just one dialog to open the file.

[Bob Phillips]

Have you slotted you're code in, is that causing the problem?

[Anomandaris]

no, even without my code it doesnt work

[Bob Phillips]

Strange, it worked fine for me.

[Anomandaris]

yeah its weird, the code makes sense, it should work..I'll fiddle around with it and see if i get lucky

[Bob Phillips]

Or post your workbook!

[Anomandaris]

Hey I just tried your code with a different workbook and it worked. There was probably smthn wrong with the other workbk.

[Anomandaris]

Hey so I tried to add in Date and time, but it doesnt show up in teh dialog box, Its always saving as "Excel Files (*.xls), *.xls",

how do i adjust it?

Dim Filename As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim strDate As String
strDate = Format(Date, "dd-mm-yy") & "." & Format(Time, "hh-mm-ss")


Filename = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If Filename <> False Then

Set wbSource = Workbooks.Open(Filename)
wbSource.Worksheets("Roll").Copy
Set wbTarget = ActiveWorkbook

'your code



Filename = Application.GetSaveAsFilename("Excel Files (*.xls), *.xls")
If Filename <> False Then

wbTarget.SaveAs "Aro1" & "" & strDate

wbTarget.Close
wbSource.Close SaveChanges:=False
End If
End If


thanks

[Bob Phillips]

If you are specifying the date, you don't need the dialog



Dim Filename As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim strDate As String
strDate = Format(Date, "dd-mm-yy") & "." & Format(Time, "hh-mm-ss")


Filename = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If Filename <> False Then

Set wbSource = Workbooks.Open(Filename)
wbSource.Worksheets("Roll").Copy
Set wbTarget = ActiveWorkbook

'your code



wbTarget.SaveAs "Aro1" & "" & strDate

wbTarget.Close
wbSource.Close SaveChanges:=False
End If

[Anomandaris]

Thanks a lot buddy!