Ask a Question

Read from and write to the same Excel file using VBScript

SOLVED
jgoetz
Occasional Contributor

Read from and write to the same Excel file using VBScript

I am trying to find a solution to the following issue, but what I have found is not working for me so I am not sure what I am missing. 

 

I have a VBScript that opens and excel spreadsheet that has a header row of keyword script names, saves that name as a variable and in the next row it has an indicator if that script should be run or not.  If the indicator is Y then it uses the name variable to call the keyword test. It loops through these for each test case ID #. 

 

TC#Global_LoginGlobal_PrimaryNav_OpenCom_Cust_CreateCust
Gen1.0YYY
Gen2.0YYY
Cust1.0NNN

 

What I would like to add to this is that once the script is ran, it changes the indicator to "P" to indicate that the script ran. 

 

TC#Global_LoginGlobal_PrimaryNav_OpenCom_Cust_CreateCust
Gen1.0PPP
Gen2.0PPP
Cust1.0NNN

 

The problem I am running into is when it saves the file, it creates a new file in my My Documents folder, not to the same file and folder.  After looking at this, when the script open's the excel file it is opening it as read only, so it won't save any changes to it. I need to saved to the same file/folder because 1.  if the script stops half way through the test cases, I want to have it updated which test cases ran, instead of me doing it manually so I can start where they left off, and 2. I want to use those results later when running a report.

 

I have tried several things suggested in other topics, including SaveAs with the the current file's path, closing the excel file before opening it again to write to it, etc., but they are not working for me. I get that the file is read only, cannot write to readonly, etc.

 

Any help would be appreciated.  What am I missing?  I know some VBScript and newer to TestComplete, but I am still learning. 

 

This is my script.  I am using Excel 2007.

 

Sub testSuiteRun(scriptRunType, customerType, runComponentArea)
  
  Dim excelApplication, projectPath
  Dim sheetName
  
  'Open and set Excel objects, spreadsheet, sheet, row count and column count

  projectPath = Project.Path
  Set excelApplication = Sys.OleObject("Excel.Application")
  set book = excelApplication.Workbooks.Open (projectPath & "Test Data\TC_Script_Run.xlsx")
  excelApplication.Visible = True
  
  sheetName = runComponentArea
  Set sheet = book.Sheets(sheetName)
  rowCount = sheet.UsedRange.Rows.Count
  colCount = sheet.UsedRange.Columns.Count  
  tcCount = 1
  recordResults = "Y"
  
  'excelApplication.DisplayAlerts = False
  
  'Loop through rows of spreadsheet ignoring the header row
  For r = 2 To rowCount
    
    'check if active row = run type and customer type 
    If (sheet.Cells(r, 1) = scriptRunType And sheet.Cells(r, 2) = customerType) Then
      
      'Loop through component script columns     
      For c = 4 To colCount
        
        'Set test case name to the header cell (row 1) for active column and the run indicator for active column and row
        testCaseName = VarToString(sheet.Cells(1, c)) 
        runInd = VarToString(sheet.Cells(r, c)) 
         
        'Check if test case name is not blank for active col to eliminate extra blank columns   
        If testCaseName <> "" Then
          
          'check if run indicator is Y and if so then call that component script   
          If runInd = "Y" Then
            
            If testCaseName = "Global_PrimaryNav_Open" Then
          
               Log.Message(testCaseName) 
               'Call Eval("KeywordTests." + testCaseName + ".Run(""" + runComponentArea + """)")                 
          
            Else     
          
              Log.Message(testCaseName) 
              'Call Eval("KeywordTests." + testCaseName + ".Run")     
          
            End If
          
            If recordResults = "Y" Then
            
              sheet.Cells(r, c).Value = "P"
              book.Save
              'book.SaveAs (projectPath & "Test Data\TC_Script_Run.xlsx")
              
            End If
              
          End If
        
        End If
    
      Next
  
    End If
    
  Next

  'quit excel
  excelApplication.Quit
  excelApplication.DisplayAlerts = True

End Sub

 

1 ACCEPTED SOLUTION

Accepted Solutions
jgoetz
Occasional Contributor

Re: Read from and write to the same Excel file using VBScript

Thanks for the suggestions.  The suggestions about write permissions got me thinking and it turns out that because I am using the same file in a datadriven loop in the keyword script that calls the script above, to pull data from another tab, that was the issue.  I seperated out the tabs and used a different file for the datadriven loop, and now I don't have an issue with read write. 

View solution in original post

6 REPLIES 6
NisHera
Valued Contributor

Re: Read from and write to the same Excel file using VBScript

did you go through this ?

dmiscannon
Frequent Contributor

Re: Read from and write to the same Excel file using VBScript

When you use SaveAs what is the result? You state it is not working, but not what is happening. Is the file saved but not to the projectPath? I use VB script and frequently write results back to Excel without issue using both Save and SaveAs.

 

One other thing to check if your projectPath is on your local hard drive. Where I work, our local computer's hard drive is locked down (unless you are a local admin) where you cannot save any files to the local hard drive. All files are saved to a network drive even if indicating to save it to c:\.... Just one more thing to consider.

Colin_McCrae
Community Hero

Re: Read from and write to the same Excel file using VBScript

Yep. I'm the same as @dmiscannon read and write to Excel all the time no problem.

 

This sounds more like a permissions issue if it's opening read only in the first place. Do you normally have write access to the drive in question? And does it make any difference if you run TestComplete as Admin?

jgoetz
Occasional Contributor

Re: Read from and write to the same Excel file using VBScript

Thanks for the suggestions.  The suggestions about write permissions got me thinking and it turns out that because I am using the same file in a datadriven loop in the keyword script that calls the script above, to pull data from another tab, that was the issue.  I seperated out the tabs and used a different file for the datadriven loop, and now I don't have an issue with read write. 

View solution in original post

Raj27
Occasional Contributor

Re: Read from and write to the same Excel file using VBScript

Can you share that VB script for writing data into excel sheet

TanyaYatskovska
Community Manager

Re: Read from and write to the same Excel file using VBScript

Getting data from Excel is always a tough question. There are many ways of how it can be done. It's very important to choose the best way based on the current requirements. Our SmartBear Community Leader in TestComplete, @shankar_r, shares best practices with us. Watch the interview here:

https://www.youtube.com/watch?v=3zQ_1xhokVQ&feature=youtu.be&t=552

---------
Tanya Yatskovskaya
SmartBear Community and Education Manager

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Join us in this interview with Shankar Ramasamy, a SmartBear Community Leader. Join the SmartBear Community today: https://community.smartbear.com/
cancel
Showing results for 
Search instead for 
Did you mean: