Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Unsolved
Views: 994

Mar 31st, 2009

excel vba

Expand Post »

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

Similar Threads

Sendkeys in excel vba in Visual Basic 4 / 5 / 6
Copying Tables From Word and Pasting in Excel in VBA in Visual Basic 4 / 5 / 6
Coding in Excel using VBA in Community Introductions
help with excel vba in Visual Basic 4 / 5 / 6
Find Method problem in Excel VBA.... in Visual Basic 4 / 5 / 6

arvin2006

Reputation Points: 15

Solved Threads: 4

Junior Poster in Training

Offline

69 posts
since Sep 2006

Permalink

Mar 31st, 2009

Re: excel vba

Quote ...

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?

koolsid

Reputation Points: 11

Solved Threads: 6

Light Poster

Offline

35 posts
since Feb 2005

Permalink

Mar 31st, 2009

Re: excel vba

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

cguan_77

Reputation Points: 19

Solved Threads: 115

Nearly a Posting Virtuoso

Offline

1,317 posts
since Apr 2007

Permalink

Apr 1st, 2009

Re: excel vba

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.

arvin2006

Reputation Points: 15

Solved Threads: 4

Junior Poster in Training

Offline

69 posts
since Sep 2006

Permalink

Apr 2nd, 2009

Re: excel vba

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

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

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

cguan_77

Reputation Points: 19

Solved Threads: 115

Nearly a Posting Virtuoso

Offline

1,317 posts
since Apr 2007

Permalink

Apr 2nd, 2009

Re: excel vba

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

arvin2006

Reputation Points: 15

Solved Threads: 4

Junior Poster in Training

Offline

69 posts
since Sep 2006

Permalink

Apr 3rd, 2009

Re: excel vba

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

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

cguan_77

Reputation Points: 19

Solved Threads: 115

Nearly a Posting Virtuoso

Offline

1,317 posts
since Apr 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Collection object with flexgrid

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: replacing variable data..(pls very urgent)

DaniWeb Message

Cancel Changes

User Name
Password