Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

calling excel files from visual basic

i have about 25 master excel files, which i want to call from a single vb form. also whenever , i call one of these files, only a copy of that files should be activated, so that even if i make changes, it will not affect the master file. after doing some changes, i want to save the modified file as a different name.

0
kamal3965
Asked:
kamal3965
  • 5
  • 4
  • 2
  • +2
1 Solution
 
bruintjeCommented:
Hi kamal3965,

Save all your excel master files as excel templates
then let your program load them all up in a combo or listbox
and then use a bit of code like

Private Sub Command1 Click()

  Dim xlApp As Excel.Application
  Dim xlBook As Excel.Workbook
  Dim xlSheet As Excel.Worksheet
 
  Set xlApp = CreateObject("Excel.Application")
  Set xlBook = xlApp.Workbooks.Open(App.Path & "\my.xlt")
  Dim i As Integer
  For i = 1 To 10
    xlBook.Worksheets("My Sheet").Copy  
          before:=xlBook.Worksheets("My Sheet")
    Set xlSheet = xlBook.ActiveSheet
    xlSheet.Range("A1").Offset(1, 0).Value = i
  Next i
 
  xlApp.Application.DisplayAlerts = False
  xlBook.Worksheets("My Sheet").Activate
  xlApp.ActiveWindow.SelectedSheets.Delete
 
  xlBook.Worksheets.Add before:=xlBook.Worksheets("My Sheet (2)")
 
  xlBook.SaveAs FileName:=App.Path & "\my.xls", FileFormat  
            :=xlNormal, Password:="", WriteResPassword:="",  
            ReadOnlyRecommended:=False, CreateBackup:=False
           
  xlBook.Close
  xlApp.Close
 
  xlApp.Quit
  Set xlApp = Nothing
  Set xlBook = Nothing
  Set xlSheet = Nothing

End Sub

HAGD:O)Bruintje
0
 
kamal3965Author Commented:
as i am new to visual basic how do i let my program load excel templates in a combo or listbox

0
 
Richie_SimonettiIT OperationsCommented:
If you have all your 25 files in one folder, Dir$ function would populate your combo/list box in an easy way.

dim ffile as string
const MYFOLDER as string="c:\mytemplates\"

ffile=dir$(MYFOLDER & "*.xlt",vbarchive) ' or *.xls
do while ffile<>""
   combox1.additem MYFOLDER & ffile
   ffile=dir$()
loop
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Richie_SimonettiIT OperationsCommented:
And instead of .Open use .Add method. This will ensure that you open a copy not "real" one.
0
 
kamal3965Author Commented:
how do you execute a excel file, which i am selecting a list/combo box.

i.e. i should execute only that excel file which i am selecting in list box/combo box


0
 
Richie_SimonettiIT OperationsCommented:
As bruintje already told you but instead of .Open, use .Add method.

Dim xlApp As Excel.Application
 Dim xlBook As Excel.Workbook
 Dim xlSheet As Excel.Worksheet
 
 Set xlApp = CreateObject("Excel.Application")
 Set xlBook = xlApp.Workbooks.Add(combobox1.text)
...
....
....
'more code
......
....
0
 
kamal3965Author Commented:
Thanks everyone who have responded to my query
0
 
Richie_SimonettiIT OperationsCommented:
That's fine. did you get all working OK?
0
 
kamal3965Author Commented:
actually i am not able to open the excel files when i double click an excel file in a list box.
0
 
bruintjeCommented:
what did you put in the eventhandler on the double click?
0
 
Richie_SimonettiIT OperationsCommented:
As Bruintje right pointed, don't forget to add the full path to items in combo as i already told you (see my code above)
...
...
...
ffile=dir$(MYFOLDER & "*.xlt",vbarchive) ' or *.xls
...
...
0
 
DanRollinsCommented:
Hi kamal3965,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Split points between: bruintje and Richie_Simonetti

kamal3965, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
SpideyModCommented:
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange

bruintje points for you at:
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20529302.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now