Improve company productivity with a Business Account.Sign Up

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

How to create - save - write a specific excel file?

Hi experts,

I have a Sub that create an excel file and a Sub that add info in the file but it doesn't save the file and if I open a new file, the script takes that new file and write in the active file.

I would like my script to save the file in the same directory where the script strated, give the actual date and time as a name and write in that file even if I open or work in another excel file.

Can you help on this?

Thank you
Sub CreateExcelFile()
	objXL.Visible = True
	objXL.WorkBooks.Add
	'Set column width.
	objXL.Columns(1).ColumnWidth = 20
	objXL.Columns(2).ColumnWidth = 20
	objXL.Columns(3).ColumnWidth = 20
	objXL.Columns(4).ColumnWidth = 35
	
	
	''Set column headers
	objXL.Cells(1, 1).Value = "Computer Name"
	objXL.Cells(1, 2).Value = "Ping Status Code"
	objXL.Cells(1, 3).Value = "Logon Name"
	objXL.Cells(1, 4).Value = "Folder Exists"
	
	''Format text (bold)
	objXL.Range("A1:D1").Select
	objXL.Selection.Font.Bold = True
	objXL.Selection.Interior.ColorIndex = 1
	objXL.Selection.Interior.Pattern = 1 ''xlSolid
	objXL.Selection.Font.ColorIndex = 2
	''Left Align text
	objXL.Columns("B:B").Select
	objXL.Selection.HorizontalAlignment = &hFFFFEFDD '' xlLeft
	objXL.Cells(2, 1).Select
End Sub
 
Sub AddToExcelFile(strName, strCode, strReg, strFolder)
    objXL.Cells(intIndex, 1).Value = strName
    objXL.Cells(intIndex, 2).Value = strCode    
    objXL.Cells(intIndex, 3).Value = strReg
    objXL.Cells(intIndex, 4).Value = strFolder
    intIndex = intIndex + 1
    objXL.Cells(intIndex, 1).Select
End Sub

Open in new window

0
EricPelletier
Asked:
EricPelletier
1 Solution
 
Shift-3Commented:
Here is a modified version of the first Sub which saves the spreadsheet with a name equal to the date and time.

I'm not sure how the two are supposed to work together, but you should be able to make similar changes to the second one.
Sub CreateExcelFile()
	strFile = Date & " " & Time & ".xls"
	strFile = Replace(strFile, "/", "-")
	strFile = Replace(strFile, ":", "-")
	
	Set objXL = CreateObject("Excel.Application")
	objXL.Visible = True
	Set objWorkbook = objXL.Workbooks.Add()
	Set objWorksheet = objWorkbook.Worksheets(1)
 
	'Set column width.
	objXL.Columns(1).ColumnWidth = 20
	objXL.Columns(2).ColumnWidth = 20
	objXL.Columns(3).ColumnWidth = 20
	objXL.Columns(4).ColumnWidth = 35
 
 
	''Set column headers
	objXL.Cells(1, 1).Value = "Computer Name"
	objXL.Cells(1, 2).Value = "Ping Status Code"
	objXL.Cells(1, 3).Value = "Logon Name"
	objXL.Cells(1, 4).Value = "Folder Exists"
 
	''Format text (bold)
	objXL.Range("A1:D1").Select
	objXL.Selection.Font.Bold = True
	objXL.Selection.Interior.ColorIndex = 1
	objXL.Selection.Interior.Pattern = 1 ''xlSolid
	objXL.Selection.Font.ColorIndex = 2
	''Left Align text
	objXL.Columns("B:B").Select
	objXL.Selection.HorizontalAlignment = &hFFFFEFDD '' xlLeft
	objXL.Cells(2, 1).Select
	objWorkbook.SaveAs(strFile)
End Sub

Open in new window

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now