excel vba

[arvin2006]

hi there, i am new to excel VBA. can anyone help me how to copy all the data in excel to another excel sheet by means of visual basic. for example excel1 and excel2.. after i locate excel1, then when i click copy button, it will copy all the data in excel1 and open a new sheet, excel2 having the same data contents exactly as excel1.. any idea.. thanks

[koolsid]

same data contents exactly as excel1

Why don't you make a copy instead? Or do you just want to copy sheet1 from Excel1 to Excel2?

[cguan_77]

hi arvin2006, if you just want exactly same contents of your excel1 to excel2..you can just use file->save as.. or explain further what you really want.. so guys around here can help you accomplish what you really want to do...

[arvin2006]

Here is the idea, i have an excel file which contains data(say excel1). In my visual basic form, i have 'locate file' button, (for locating excel1). After i locate excel1 for whatever path it is located, when i press 'process' button', it will automatically create a new excel file (say excel2) which now contains same data as excel1 BUT with formatted data, which means data in excel2 is now processed.. For example, the columns in excel2 was set autofit, the fonts for selected rows/columns were bold, having font color red or blue, with fontsize 16, and the data for a particular columns were computed by means of setting formula to specified columns etc. etc.. In short, manual formatting and computing in excel (so no more human effort) were now automated thru visual basic, i think it can be done with visual basic (excel vba for an instance). I'm new to excel vba and now ready to explore further as i can, and asking whosoever here professional in excel vba if it is possible to create a new excel file with the same data as old excel file. For now, i am just on formatting fontcolor, sizes and still on the way on how to set the formula in excel thru visual basic.. Hope you guys know what i mean. Thank you for reading and hope i can get idea for anyone. God bless..

[cguan_77]

hi arvin2006, check this simple vbscript..it will copy excel1 to excel2.. just change the path as necessary..

Dim FSO 
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile "c:\excel1.xls", "c:\excel2.xls"  'change the path as necessary
                                               'copy excel1 to excel2


Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run ("C:\excel2.XLS")                  'activate or runs the copied file

and on you excel2 workbook copy this code

'to do formatting on selected cells
Private Sub Workbook_Open()
    Range("A1").Select
    Selection.Font.ColorIndex = 3
    Range("A2").Select
    Selection.Font.Bold = True

End Sub

[arvin2006]

hi, the one you provided is about scripting, but anyway, thank you for giving some suggestions. it can help. i am focusing on excel visual basic automation. thanks....

[cguan_77]

hi arvin, you can use this on vba to copy file...

dim str_SourceFile as String
dim str_DestinationFile as String

'str_SourceFile path of the source file
'str_DestinationFile path of the destination file

FileCopy str_SourceFile, str_DestinationFile