DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

excel vba

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site

•

Join Date: Sep 2006

Posts: 65

Reputation: arvin2006 is an unknown quantity at this point

Solved Threads: 4

arvin2006

Offline

Junior Poster in Training

excel vba

Mar 31st, 2009

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

•

Join Date: Feb 2005

Posts: 35

Reputation: koolsid is an unknown quantity at this point

Solved Threads: 6

koolsid

Offline

Light Poster

Re: excel vba

Mar 31st, 2009

•

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?

A good excercise for the Heart is to bend down and help another up...

Please Mark your Thread "Solved", if the query is solved...

==>If a post has helped you then Please Rate it!

•

Join Date: Apr 2007

Posts: 1,114

Reputation: cguan_77 has a little shameless behaviour in the past

Solved Threads: 91

cguan_77

Offline

Veteran Poster

Re: excel vba

Mar 31st, 2009

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...

•

Join Date: Sep 2006

Posts: 65

Reputation: arvin2006 is an unknown quantity at this point

Solved Threads: 4

arvin2006

Offline

Junior Poster in Training

Re: excel vba

Apr 1st, 2009

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..

Last edited by arvin2006; Apr 1st, 2009 at 8:46 pm.

•

Join Date: Apr 2007

Posts: 1,114

Reputation: cguan_77 has a little shameless behaviour in the past

Solved Threads: 91

cguan_77

Offline

Veteran Poster

Re: excel vba

Apr 2nd, 2009

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

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
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
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

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
'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
'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

•

Join Date: Sep 2006

Posts: 65

Reputation: arvin2006 is an unknown quantity at this point

Solved Threads: 4

arvin2006

Offline

Junior Poster in Training

Re: excel vba

Apr 2nd, 2009

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....

•

Join Date: Apr 2007

Posts: 1,114

Reputation: cguan_77 has a little shameless behaviour in the past

Solved Threads: 91

cguan_77

Offline

Veteran Poster

Re: excel vba

Apr 3rd, 2009

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

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
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
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