?
Solved

How to open Excel file using VB?

Posted on 2004-11-23
9
Medium Priority
?
36,828 Views
Last Modified: 2012-06-27
Part of my VB program is written with :

'(1) open Excel files
   currentDate = Format(Date, "YYYY") & " " & Format(Date, "MM") & " " & Format(Date, "DD") & " " & "0000" & " " & "(WIDE)"
   filePath = "C:\nbutane\rsview\DLGLOG\RSVIEW\" & currentDate & ".dbf"


When the VB executes, it'll open the file called      2001 11 23 0000 (Wide)    with Excel.

My problem is when the file name changed to      2001 11 23 0001 (Wide)     , the VB can't access it because it's reading from '0000'. Sometimes the file will change to '0002' .... and so on.

Please help. Thanks.
0
Comment
Question by:ckchew666
  • 5
  • 3
9 Comments
 
LVL 11

Expert Comment

by:pratap_r
ID: 12655803
are you looking for a dynamic way of finding if a file exists? for example 2001 11 23 0001 (Wide).dbf in your case?

0
 
LVL 11

Expert Comment

by:pratap_r
ID: 12655940
try this, the GetFileName funtion returns you the file that exists on the disk, you might want to change the counters accordingly

Private Sub Form_Load()
MsgBox GetFileName
End Sub

Public Function GetFileName()
On Error Resume Next
Dim str1$, str2$, ln%
str1 = Format(Date, "YYYY") & " " & Format(Date, "MM") & " " & Format(Date, "DD") & " "
For i = 0 To 99
    str2 = str1 + Format(i, "0000") & " " & "(WIDE)"
    filepath = "C:\nbutane\rsview\DLGLOG\RSVIEW\" & str2 & ".dbf"
    ln = FileLen(filepath)
    If Err.Number <> 53 Then
        GetFileName = filepath
        Exit Function
    End If
    Err.Clear
Next
End Function


Have Fun!
Pratap
0
 

Author Comment

by:ckchew666
ID: 12655953
I want my VB program to be able to find 2001 11 23 0001 (Wide).dbf  and open it when 2001 11 23 0000 (Wide).dbf  does not exists
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Expert Comment

by:pratap_r
ID: 12656032
thats what the GetFileName returns, it starts checking from 2004 11 23 0000 (Wide).dbf to 2004 11 23 0099 (Wide).dbf and returns on the first found file

so if 2004 11 23 0000 (Wide).dbf does not exist and 2004 11 23 0001 (Wide).dbf exist then thats what the function call will return.. the fullpath to 2004 11 23 0001 (Wide).dbf


Have Fun!
Pratap
0
 

Author Comment

by:ckchew666
ID: 12656041
SOrry, I'm very new to VB, I don't really understand ur sample codes, tho` it might be very easy for you :)

Looks like ur VB sample is able to search for file with number 0000 to 0099. Il'll keep searching until it found a number, am i right? But how do I integrate it to my VB codes?

Here's my entire VB program.

_____________________________________________________________________________



Public Sub btnCreateExcel_Click()
On Error GoTo Err_btnCreateExcel_Click

   Dim objExcel As Object
   Dim objExcel_data As Object
   Dim currentDate As String
   Dim wbTarget As Object
   Dim wbSource As Object
   Dim filePath As String
     

'(1) open Excel files
   currentDate = Format(Date, "YYYY") & " " & Format(Date, "MM") & " " & Format(Date, "DD") & " " & "0000" & " " & "(WIDE)"
   filePath = "C:\nbutane\rsview\DLGLOG\RSVIEW\" & currentDate & ".dbf"

'(2) open the source workbook as read only and open target workbook with write
   Set objExcel_data = CreateObject("Excel.Application")
   Set wbSource = objExcel_data.Workbooks.Open(filePath).Sheets(currentDate)

   Set objExcel = CreateObject("Excel.Application")
   Set wbTarget = objExcel.Workbooks.Open("C:\nbutane\rsview\report\" & "FC Daily Report" & ".xls").Sheets(1)

     



'(3) extract data from source and put into target
    With wbSource
        '******************************************
        '''''''''''(Rows, Column)''''''''''''''''''
        '******************************************
        Dim i As Integer
        i = 2 'always start with 2 base on the source sheet
        Do While (wbSource.cells(i, 2) <> "")               ' do below if something
           
           
           
  If CDate(.cells(i, 2).Value) >= CDate("6:00:00") And CDate(.cells(i, 2).Value) <= CDate("6:02:00") Then
               wbTarget.cells(5, 3).Value = .cells(i, 5).Formula
               wbTarget.cells(5, 4).Value = .cells(i, 7).Formula
               wbTarget.cells(5, 5).Value = .cells(i, 9).Formula
               wbTarget.cells(5, 6).Value = .cells(i, 11).Formula
               wbTarget.cells(5, 7).Value = .cells(i, 13).Formula
               wbTarget.cells(5, 8).Value = .cells(i, 15).Formula
               wbTarget.cells(5, 9).Value = .cells(i, 17).Formula
                                     
                             
  ElseIf CDate(.cells(i, 2).Value) >= CDate("5:00:00") And CDate(.cells(i, 2).Value) <= CDate("5:02:00") Then
               wbTarget.cells(28, 3).Value = .cells(i, 5).Formula
               wbTarget.cells(28, 4).Value = .cells(i, 7).Formula
               wbTarget.cells(28, 5).Value = .cells(i, 9).Formula
               wbTarget.cells(28, 6).Value = .cells(i, 11).Formula
               wbTarget.cells(28, 7).Value = .cells(i, 13).Formula
               wbTarget.cells(28, 8).Value = .cells(i, 15).Formula
               wbTarget.cells(28, 9).Value = .cells(i, 17).Formula
           
           End If
           i = i + 1   'go to next row
       Loop            'stop when no more data
   End With

'(4) save the target excel file and close both the target and source.
   wbSource.Application.Windows(currentDate & ".dbf").Visible = True
   wbSource.Application.ActiveWorkbook.Close
   objExcel_data.Quit


  'set target path
   wbTarget.Application.Windows("FC Daily Report.xls").Visible = True
   
  'to print out the source report
   objExcel.Workbooks(1).PrintOut Copies:=1, Collate:=True
   
  'after target is printed, save, close and exit Excel
   wbTarget.Application.ActiveWorkbook.Save
   wbTarget.Application.ActiveWorkbook.Close
   objExcel.Quit

   Set wbSource = Nothing  'free memory
   Set wbTarget = Nothing

     
   'Summary Excel File to be viewable (temporary not in use)
'   Set objExcel = CreateObject("Excel.Application")

   '(temporary not in use)
'   objExcel.Workbooks.Open ("C:\nbutane\rsview\report\FC Daily Report.xls")
     
   'open summary file with Excel (temporary not in use)
'   objExcel.Application.Visible = True
     
   
Err_btnCreateExcel_Click:
   'only display message when error encountered
   If Err.Number <> 0 Then
       MsgBox Err.Description
   End If

End Sub
__________________________________________________________________________

Hope you can show me. Thanks.
0
 
LVL 11

Accepted Solution

by:
pratap_r earned 600 total points
ID: 12656235
sorry ckchew.. my mistake, i should have explained the code... but you got it right.. thats what the code does..

hmm now coming to changing your code.. change your line

'(1) open Excel files
   currentDate = Format(Date, "YYYY") & " " & Format(Date, "MM") & " " & Format(Date, "DD") & " " & "0000" & " " & "(WIDE)"
   filePath = "C:\nbutane\rsview\DLGLOG\RSVIEW\" & currentDate & ".dbf"
'(2) open the source workbook as read only and open target workbook with write


to

'(1) open Excel files
On Error Resume Next
Dim ln%
For i = 0 To 99
   currentDate = Format(Date, "YYYY") & " " & Format(Date, "MM") & " " & Format(Date, "DD") & " " & Format(i,"0000") & " " & "(WIDE)" 'notice the Format(i, "0000")
    filepath = "C:\nbutane\rsview\DLGLOG\RSVIEW\" & currentDate & ".dbf"
    ln = FileLen(filepath) 'a dummy function call to get the filesize, not that we are going to use it anywhere.. just to raise an error if the file does not exist
    If Err.Number <> 53 Then 'err.number is set to 53 if we get a file not found error
        GetFileName = filepath 'so the error was not 53 so we assume the file is present
        exit for 'come out of the for loop
    End If
    Err.Clear '<--clear the previous error
Next
on error goto 0 '<-- this will disable the resume next error handling that we enabled earlier in the code
'(2) open the source workbook as read only and open target workbook with write


let me know if you face any problems :-)

Have Fun!
Pratap
0
 

Author Comment

by:ckchew666
ID: 12656380
Hi Pratap,

You got it work!! You are da` man!
This is the fastest guide I've ever get. Great job! :)

For i = 0 To 99
 This one I've change it to p (as a token of appreciation p = pratap), because i is already defined in the program.

Thanks again.

CK Chew
0
 
LVL 11

Expert Comment

by:pratap_r
ID: 12657231
my pleasure :-D

Have Fun!
Pratap
0
 

Expert Comment

by:Shobhit
ID: 13903849
You should use "ShellExecute" API to open an Excel File. Accept the path of the excel file and pass this parameter to the ShellExecute API function.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Simple Linear Regression
Introduction to Processes

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question