Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Overwriting Existing Excel File -- How To Disable Excel Dialog Box


Overwriting Existing Excel File -- How to disable Excel Dialog box ...?




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Overwriting An Existing Excel File
I've success in creating and saving a new Excel workbook using VBA codes.

But when the system detects a file with the same name it always pops up a warning message from Excel, prompting the user whether to overwrite the file or not. I don't want that. I simply want it to overwrite without warning. This gives me some headache.

I'd tried the Excel workbook method wrkbookx.saveAs("filename", True) and I am aware of setting the property called "AlertBeforeOverwriting" of an Excel Application object to False. Neither works.

Who has this experience? Some short sample codes will help.

How To Force Overwriting Of Existing Excel File
Hi All,
I have this code, which creates new Excel worksheet and dumps data from recordset and saves Excel file with passed filename.

  Set xlApp = New Excel.Application
  Set xlBook = xlApp.Workbooks.Add
  Set xlSheet = xlBook.Worksheets.Add

  'loop with data goes here

  'save file
  xlSheet.SaveAs strFileName

 It works fine except if there's existing excel file with the same name.
Excel pops-up message box asking if user wants to replace existing file and options are: Yes, No, Cancel.

If Yes, no problem, if No or Cancel then VB gives an error: Method 'SaveAs' of object '_Worksheet' failed.

I want to force file replacement in my VB code, but don't know how. Seems there no parameters to xlSheet.SaveAs.

Any ideas?
Thanks much.
Steve



Print Existing Excel File Using Common Dialog
What I need is to click on my command button and open a common dialog box for printing. Then print a specific excel file I created. What code do I add to print my excel file.



Private Sub cmdPrint_Click()

Dim printDlg As PrinterDlg
Set printDlg = New PrinterDlg

printDlg.PrinterName = Printer.DeviceName
printDlg.DriverName = Printer.DriverName
printDlg.Port = Printer.Port
printDlg.PaperBin = Printer.PaperBin

Printer.TrackDefault = False
printDlg.CancelError = True
Dim NewPrinterName As String
Dim objPrinter As Printer
Dim strsetting As String
NewPrinterName = UCase$(printDlg.PrinterName)
On Error GoTo ErrorHandler
printDlg.ShowPrinter Me.hWnd

If Printer.DeviceName <> NewPrinterName Then
For Each objPrinter In Printers
If UCase$(objPrinter.DeviceName) = NewPrinterName Then
Set Printer = objPrinter
End If
Next
End If
Printer.Copies = printDlg.Copies
Printer.Orientation = printDlg.Orientation
Printer.ColorMode = printDlg.ColorMode
Printer.Duplex = printDlg.Duplex
Printer.PaperBin = printDlg.PaperBin
Printer.PaperSize = printDlg.PaperSize
Printer.PrintQuality = printDlg.PrintQuality
With Printer
Debug.Print .DeviceName
If .Orientation = 1 Then
strsetting = "Portrait. "
Else
strsetting = "Landscape. "
End If
Debug.Print "Copies = " & .Copies, "Orientation = " & _
strsetting
If .ColorMode = 1 Then
strsetting = "Black and White. "
Else
strsetting = "Color. "
End If
Debug.Print "ColorMode = " & strsetting
If .Duplex = 1 Then
strsetting = "None. "
ElseIf .Duplex = 2 Then
strsetting = "Horizontal/Long Edge. "
ElseIf .Duplex = 3 Then
strsetting = "Vertical/Short Edge. "
Else
strsetting = "Unknown. "
End If
End With
ErrorHandler:
If Err.Number = 32755 Then
Exit Sub
End If

Overwriting Excel File Without User Knowing
I need a VB6 program to open an excel file and take a particular cell and if the contents are >0 then subtract 1 and save the file again without the user knowing. I have used the "Application.DisplayAlerts = False" command and it works the first time, but then the next time it gives an error that it is a read only file and crashes the program.
Does anybody know how to make VB overwrite a file without the user knowing and not making it a read only file?

Overwriting An Excel File Without The User Knowing
I need for my VB6 program to open an excel file and if a number in a particular cell is >0 then subtract 1 from that number and save the file. When I run the program, it keeps asking if I want to overwrite the spreadsheet. Does anybody know how to make VB overwrite a file without prompting the user? I need it to save the file without the user being prompted.

How To Open An Existing Excel File And Add A Record To The End Of The Existing Record And Save It An
How to open an existing excel file and add a record to the end of the existing record and save it and how to delete a row (record) in excel-Urgent-Thanks

Please give me a bit detail the code, that is, copy it and it can be run.

thanks in advance!!!

How Can I Get The File Existing From Excel Using VB6.0?
Anybody who knows that get the file existing from Excel using VB ,Please answer me.Please give me some details.Thanks.

Opening An Existing Excel File From VB6...
I have code (thanks to Mike_R) that can create an Excel file and populate it's cells. The problem is that I would also like to allow the user to open and existing Excel file and append data to the end. Any idea how to do this?


Here is my current code:
Code:
Private Sub SaveTableInExcel()
Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim vFileName As Variant
Dim lExcelRow As Long, lExcelCol As Long
Dim lRow As Long, lCol As Long

If (grdMessageArm1.Row <> 0 Or grdMessageArm2.Rows <> 0) Then
Set oExcel = New Excel.Application

oExcel.DisplayAlerts = True
Retry_Filename:
vFileName = oExcel.GetSaveAsFilename("RejectTableData.xls", "Workbook (*.xls),*.xls", , "Save Reject Table Data", "Save")
If vFileName = False Then Exit Sub

Set oWB = oExcel.Workbooks.Add
On Error GoTo File_error
Create_File:
oWB.SaveAs FileName:=vFileName
Add_Info:
Set oWS = oWB.Worksheets(1)

oWS.Name = "Reject Table Data"

lExcelRow = 1
oWS.Rows(lExcelRow).Font.Bold = True
oWS.Cells(lExcelRow, 1) = "Reject Arm"
oWS.Cells(lExcelRow, 2) = lblRejectCode1
oWS.Cells(lExcelRow, 3) = lblSystemID1
oWS.Cells(lExcelRow, 4) = lblErrorMsg1
oWS.Cells(lExcelRow, 5) = lblSlotAssigned1
For lRow = 0 To grdMessageArm1.Rows - 1
lExcelRow = lExcelRow + 1
lExcelCol = 1
oWS.Cells(lExcelRow, lExcelCol) = 1
lExcelCol = lExcelCol + 1
For lCol = 0 To grdMessageArm1.Cols - 1
lExcelCol = lExcelCol + 1
oWS.Cells(lExcelRow, lExcelCol) = grdMessageArm1.TextMatrix(lRow, lCol)
Next
Next
For lRow = 0 To grdMessageArm2.Rows - 1
lExcelRow = lExcelRow + 1
lExcelCol = 1
oWS.Cells(lExcelRow, lExcelCol) = 2
lExcelCol = lExcelCol + 1
For lCol = 0 To grdMessageArm1.Cols - 1
lExcelCol = lExcelCol + 1
oWS.Cells(lExcelRow, lExcelCol) = grdMessageArm2.TextMatrix(lRow, lCol)
Next
Next

oWS.Columns("A:E").AutoFit

oWB.Save
oWB.Close
oExcel.Workbooks.Close
oExcel.Quit
Set oWS = Nothing
Set oWB = Nothing
Set oExcel = Nothing
Exit Sub
File_error:
Set fMB = New dlgMessageBox
fMB.Caption = "Save File Error"
fMB.pbIcon.Picture = LoadResPicture("Exclamation", vbResIcon)
fMB.lblMessage = "(" & Err.Description & _
") error occured while trying to create Reject Table."
fMB.cmdButton(APPEND).Caption = "Append"
fMB.cmdButton(REPLACE).Caption = "Replace"
fMB.cmdButton(NEWFILE).Caption = "New"
fMB.cmdButton(CANCEL).Caption = "Cancel"
fMB.Show vbModal

Select Case fMBRetVal
Case APPEND
[color=DarkRed]oWB.Open FileName:=vFileName[/color]
oExcel.
Err.Clear
Resume Add_Info
Case REPLACE
Kill vFileName
Err.Clear
Resume Create_File
Case NEWFILE
Err.Clear
Resume Retry_Filename
Case Else
Err.Clear
End Select
Set oWS = Nothing
Set oWB = Nothing
Set oExcel = Nothing
End If
End Sub

The section highlighted in Red is what I would like to do...

Thanks,
Tim

How Can I Open An Existing Excel File?
How can I open an existing Excel file?For this I have some code like bellow but as I now I need to create here an Object of Excel so that I could find any date from there.
Could any one help me here?

Private Sub ComBrowse_Click()

On Local Error GoTo Open_Error

CommonDialog1.CancelError = True
CommonDialog1.Filter = "File Description (*.xls)|*.xls" '|"

CommonDialog1.FilterIndex = 1
CommonDialog1.ShowOpen

FileName = CommonDialog1.FileName

If Len(FileName) = 0 Then

MsgBox "No files selected"
Exit Sub
End If
Label1.Caption = FileName


Open_Error:
End Sub

Thanks in advance!

Checking For An Existing Excel File.
Hey,
I'm using an excel file as a template, the program opens the file does what it needs to do and then saves it under a different name in a different directory. My question is how do i go and check to see if the file exists on the user's machine and if it doesn't then grab the file off a predetermined location on the network, and transfer it to the user's machine?

Thanks
Andy

Can't Open An Existing Excel File With VBA
From Access 2003, I am trying to open an existing excel spreadsheet and place access data into cells and then save to a new file.

My code works if I just .add a new workbook and loop through the data and close.

It fails with an error 1004 when I try to open an existing file

This Works:
.. '(setup code here)
With xlApp
.Visible = True
.DisplayAlerts = False
Set xlBook = .Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
... '(the rest of my code)

This fails at Set xlBook = .Workbooks.Open("k:BTdef.xls")

.. '(setup code here)
With xlApp
.Visible = True
.DisplayAlerts = False
Set xlBook = .Workbooks.Open ("k:BTdef.xls")
Set xlSheet = xlBook.Worksheets(1)
... '(the rest of my code)

Do I have to reference something else?

The existing file has data in the first row that is color coded header info. I just add names and other info from a query.

Thanks,
Dave

Opening Existing Excel File
Hello programmers,

Can you tell me a quick and easy way of opening an existing Excel file in
VB6? I am using GetObject() and it does not work. I have updated the ROT
table and still the file is not opened. FYI, CreateObject() does work. Also,
the example straight out of MSDN does not work(?!).

Thanks,
Mario
IBC
Confidentiality Notice
The information contained in this email is privileged and confidential. It
is intended solely for the addressee. Access to this email by anyone else is
unauthorized. If you are not the intended recipient, any disclosure,
copying, distribution or any action taken or omitted to be taken in reliance
on it, is prohibited and may be unlawful.

How To Overwrite Existing Excel File
hi how do i overwrite an existing excel file, without excel displaying a dialog file "replace existing file".?

 

Opening Existing Excel File With VB6 ?
How do I open an existing Excel file using VB6 ?
Thanks,
Charles

Reading From Existing Excel File
Hello, I need to write an app that reads zipcodes from Excel files and writes each zip as it reads it to a new text file, with a crlf after each zip.
I know how to write to my file, but I don't know how to read from an Excel file, any suggestions to get me started?

Thanks,
Sean

How Can I Open An Existing Excel File?
How can I open an existing Excel file?For this I have some code like bellow but as I now I need to create here an Object of Excel so that I could find any date from there.
Could any one help me here?

Private Sub ComBrowse_Click()

On Local Error GoTo Open_Error

CommonDialog1.CancelError = True
CommonDialog1.Filter = "File Description (*.xls)|*.xls" '|"

CommonDialog1.FilterIndex = 1
CommonDialog1.ShowOpen

FileName = CommonDialog1.FileName

If Len(FileName) = 0 Then

MsgBox "No files selected"
Exit Sub
End If
Label1.Caption = FileName


Open_Error:
End Sub

Save An Excel WB But Check For Existing File Name.
Hi All,

I have this code below which saves a file for me, but at the same time it generates a file name based on the name of the user and the date. The problem that arrises is sometimes that user saves the file more then once in a day.

So i need something that will check for an exsisting filename and then add a _1 or something like that for a new name. I guess i need sometype of do until loop but i'm not sure.

Anyway here is my present code.


Code:
Sub SaveMe1()

Dim WKB As Workbook
Set WKB = Workbooks.Add
ThisWorkbook.Sheets("Report").Copy Before:=WKB.Sheets(1)
Application.DisplayAlerts = False
For i = WKB.Sheets.Count To 2 Step -1
' Delete all but the 1st sheet
WKB.Sheets(i).Delete
Next i
Application.DisplayAlerts = True
Call SaveFile

WKB.SaveAs Filename:="C:Cash Register" & Operatorlbl & " " & Format(Date, "dd-mm-yyyy") & ".xls"
WKB.Close

End Sub

Thanks for the help

Help For Adding Data To An Existing Excel File
We tried creating a new worksheet for each new user that will use the program, but we can't manage to make Excel save and close the whole file to have the data the next time we use the program to enter data. We tried using the macros in Excel but it seems that our VB version is too old (VBA4). Can you help us finding how we can deal with different worksheets ? Thanks.

Benjamin , Luc and Cindy

Code:
'button that exports the data of the program to Excel
Private Sub Enregistrer_Click()
Dim intMsg As String
Dim Prénom As String

'Alert message
Prénom = Nom.Text
Open "Prénom.txt" For Output As #1
Print #1, Prénom, variable1
intMsg = MsgBox("En train d'écrire les données pour " & Prénom & " au fichier employes.xls ")
Close #1
intMsg = MsgBox("Les données ont été enregistrées")


Dim XLapp As Object
Set XLapp = CreateObject("Excel.Application")
XLapp.Visible = True
XLapp.workbooks.Open filename:="C:Documents and SettingsBenjaminBureauDeliriumemployes.xls"
XLapp.Range("A1").Select
With XLapp.ActiveCell


hauteur = 0

If .Range("b1") = Nom.Text Then hauteur = hauteur + 1 Else: XLapp.Sheets.Add

.offset(0, 0).Range("a1") = "Date"
.offset(0, 1).Range("a1") = "Nom"
.offset(0, 2).Range("a1") = "Tâche"
.offset(0, 3).Range("a1") = "Début"
.offset(0, 4).Range("a1") = "fin"
.offset(0, 5).Range("a1") = "Total"
.offset(0, 6).Range("a1") = "Description"


.offset(hauteur, 0).Range("a2") = jour.Text
.offset(hauteur, 1).Range("a2") = Nom.Text
.offset(hauteur, 2).Range("a2") = Tâche.Text
.offset(hauteur, 3).Range("a2") = Debut.Text
.offset(hauteur, 4).Range("a2") = fin.Text
.offset(hauteur, 5).Range("a2") = Total.Text
.offset(hauteur, 6).Range("a2") = Description.Text

.offset(1, 1).Range("a1").Select
ActiveCell.FormulaR1C1 = "Ben"
Sheets("employes").Select
Sheets("employes").Name = "employes"
Sheets("employes").Select

ActiveWorkbook.Save
ChDir "C:Documents and SettingsBenjaminBureauDelirium"
ActiveWorkbook.SaveAs filename:= _
"C:Documents and SettingsBenjaminBureauDeliriumemployes.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWorkbook.Close

End With
End Sub

Opening Existing Excel File As Read-only
Hi, I have an Access db with the below function. It's opening an existing Excel file (test.xls) as Read-only, but I do not want it to. How do I get it to open test.xls as read-write?

Code:
Dim ExApp As New Excel.Application

ExApp.Workbooks.Open Filename:="C:Documents and Settings gondekDesktop est.xls"
ExApp.Visible = True
Any help would be great. Thanks!

How To Open A Existing Word/ Excel File In Vb?
Dear Friends,

How can I open a word document file/ Excel file using cmdOpen command button?

Please let me know the code to open them. (For Ex: Read Me.Doc & Price List.xls) The both files are in the same project folder.

Regards,

Vblearner6

How To Insert Some Rows To Existing Excel File?
I searched the forum, and only found some examples of creating new excel files. How can I insert some rows to existing excel file by using VB6?

I have an excel file already which includes some sheets. In 2 sheets, I need insert some rows (with data in cells), so that the excel will compute some data based on the value in these cells.

I need do this in VB6.


thanks

Adding An Existing Excel File As A VB Object.
Hi all,
What I am trying to do is open up an existing Excel template through a
VB Form to no avail. I am trying to use an existing template and
populate the data into a SQL Server, but I don't want to just open up an
instance of the file. When a user opens the form, the form displays the
template for data input. Has anyone ever done this? I hope this makes
sense.
Kevin Baker
Nuprecon, LP
35131 SE Center Street
Snoqualmie, WA 98065
(425)602-3455
email@removed

Excel VBA - Reading From A CSV File To An Existing Worksheet
Hello

I am wondering if it would be possible to read in values from a file into an existing worksheet. In addition, I would like to read the values from a file into a worksheet and specify where on the worksheet to place the values (i.e. starting at c1). The reason for this is I would like buttons and labels on the existing worksheet. Is it possible to do this?

However, at the minute I am using the following code to read in values from a file, however it creates a new worksheet each time. Therefore I would have to create buttons every time I created a new worksheet.


Code: Public Sub Open_Workbook(OpenName As String)
  Dim oCSV As Workbook
   Open OpenName For Input As #1
 
 Set oCSV = Workbooks.Open(OpenName)
 
 oCSV.Sheets(1).Copy ThisWorkbook.Worksheets(1)
 ThisWorkbook.Worksheets(1).Name = "View All Data"
 FindSpaceReplaceAB
oCSV.Close False
 
 Set oCSV = Nothing
 Close #1
End Sub





So instead I would like to be able to read values into a worksheet "Sheet1" and rename "Sheet1" to "View All Data". However, If a sheet called "View All Data" already exists, I would like to read the values into "Sheet2" and name it "View All Data" and rename the previous sheet i.e. "Sheet1" to "View All Data Old". I hope I have made myself clear. but often I have a habit of not describing myself very well !!

I appreciate any help or suggestions in advance

Regards,
Natalie



 

VBA Insert New Worksheet (lsSheetName) Into Existing Excel File
How do I Insert a new Worksheet (lsSheetName) into an existing excel file?

ldDate = Format(Now, "mm_dd_yy_hh_mm_ampm")
lsSheetName = "Sheet " & ldDate

This piece of code works because Sheet1 was already there
vb: Conn.Execute "SELECT * INTO [Sheet1] IN '" & lsFilePathName & "'" & " 'Excel 8.0;' FROM ProjectCalculations"

This piece of code does NOT work because(lsSheetName)hasn't been created
vb: Conn.Execute "SELECT * INTO [" & lsSheetName & "] IN '" & lsFilePathName & "'" & " 'Excel 8.0;' FROM ProjectCalculations"

I want to use lsSheetName, so user can track the date/time of data
being exported from ProjectCalculations table, and don't want to use a different .xls each time

I get the error
Run-Time Error: The Microsoft Jet database engine could not find the object 'Sheet 11_14_05_06_00_PM' Make sure the object exists and that you spell its name and the path name correctly.

Besides this one issue, things work well - my first time writting code between VB6 and Excel. All my programming has been with VB6, Crystal Reports, MS-Access, SQL-Server.

My entire segment of code follows in .bas file

Thank you -- Michele

Entering Textbox Data Into An Existing Excel File
My code below enter textbox data from vb into a new excel file

I need to be able to open an existing excel file and be able to add data the same way

my guess is that I need to open the file with

MoXL.Workbooks.Open FileName:="C:myfilename.xls"

then start placing the textbox values but that is not working


Any code suggestions that will enter the data in an existing file. How it works now is that each time the command button is clicked the textbox data goes to the next excel row. That is working great. But I don't want a new file to open each time I run the program I want to be able to add the data to an existing file.



Option Explicit


Private WithEvents moXL As Excel.Application
Private WithEvents moWB As Excel.Workbook
Private WithEvents moWS As Excel.Worksheet

Private Sub Command1_Click()
Dim nNextRow As Long

nNextRow = (moWS.UsedRange.Rows.Count + 1)
moWS.Cells(nNextRow, 1).Value = Text1.Text
moWS.Cells(nNextRow, 2).Value = Text2.Text
moWS.Cells(nNextRow, 3).Value = Text3.Text
moWS.Cells(nNextRow, 4).Value = Text4.Text
moWS.Columns.AutoFit


End Sub

Private Sub Command2_Click()
' clear text boxes
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""


End Sub

Private Sub Command3_Click()
Unload Me ' exit this application
End Sub

Private Sub Command4_Click()
' toggle Excel visibility
moXL.Visible = (Not moXL.Visible)

End Sub



Private Sub Form_Load()
Set moXL = New Excel.Application
Set moWB = moXL.Workbooks.Add
Set moWS = moWB.Worksheets(1)


' set column headers
moWS.Cells(4, 1).Value = "SURNAME"
moWS.Cells(4, 2).Value = "FIRSTNAME"
moWS.Cells(4, 3).Value = "SCHOOL"
moWS.Cells(4, 4).Value = "DOB"
moWS.Cells(4, 5).Value = "Tel. No"


End Sub

Private Sub Form_Unload(Cancel As Integer)
' clean-up
Set moWS = Nothing
If Not moWB Is Nothing Then
moWB.Close
Set moWB = Nothing
End If
If Not moXL Is Nothing Then
moXL.Quit
Set moXL = Nothing
End If
End Sub

Vbscript, Sql & Write/add Record To Existing Excel File
Hi, Can someone please Assist, as i need this to work it's part of my project!
VBscript, I'm executing a SQL command then with the result data append/add the row to an existing excel file.
Please anyone please Assist!
Scipt: (code)
Dim objXL
Dim File_name
Option WScript
File_name="\srv08-za063source est.xls"
Set objXL = WScript.CreateObject("Excel.Application")
Set wb = objXL.Workbooks.Add
Set ws = objXL.Worksheets(1)
r = 2
objXL.Visible = False

Set objConn = CreateObject( "ADODB.Connection")
Set resultSet = CreateObject ("ADODB.RecordSet")
objConn.Open fcCustom.Value(Process.Company, "ConnectionsFCDB")
strSQL = "select id,ProcessID from dbo.fcEventArchive where EventHistID = 2523"
resultSet.Open strSQL, objConn, 1, 2
On Error Resume Next
resultSet.MoveFirst
Do While Not resultSet.eof
ws.Cells(r, 1).Value = (resultset(0))
ws.Cells(r, 2).Value = (resultset(1))
resultSet.MoveNext
r = r + 1
Loop
resultSet.Close
objConn.Close
Set resultSet = Nothing
Set objConn = Nothing

Opening An Existing Excel File And Writing To It Quesion
I am using a commondialog to open a excel file.
Code:
Public Sub OpenExcel()
  Dim intFileHandle As Integer
  
 'search for the file to open
  Me.CommonDialog2.DialogTitle = "Please find your excel file"
  Me.CommonDialog2.InitDir = "C:"
  Me.CommonDialog2.ShowOpen
  
 ' Obtain a random file handle
  intFileHandle = FreeFile
  
  ' Open the text file with the appropriate handle
  Open Me.CommonDialog2.FileName For Output As #intFileHandle

End Sub


After I open this file I would like to write to it. This is what I had before I changed the way my code was written. I was just creating a new excel application/workbook but now I want to write to the excel file I have chosen.
Code:
  Dim a, n As Long
  a = 20
  n = 1

  For n = 1 To UBound(arrRecords)
    ' Enter a value into cells

    ***** HERE IS WHERE I GET THE ERRORS ******
    xlWorksheet.Cells(n + 1, 1).Value = 1
    xlWorksheet.Cells(n + 1, 2).Value = 1
    xlWorksheet.Cells(n + 1, 3).Value = a
    xlWorksheet.Cells(n + 1, 4).Value = tempArray(n).string4
    xlWorksheet.Cells(n + 1, 13).Value = dvalue(n).string14
    xlWorksheet.Cells(n + 1, 20).Value = dvalue(n).string14


I understand I get the errors cause VB doesnt understand where xlWorksheet is coming from. So do i still need the following code? I am confused because, the code below is for a NEW application/workbook/worksheet or does VB know that the new workbook is actually the same workbook as the one we opened using the commondialog command?

Code:
  Dim xlApp As excel.Application
  Dim xlWorkBook As excel.Workbook
  Dim xlWorkSheet As excel.Worksheet

  ' Create the appropriate instances of Excel and create a new sheet
  Set xlApp = New excel.Application
  Set xlWorkBook = xlApp.Workbooks.Add

  'creating 2 sheets in Excel Workbook
  xlApp.SheetsInNewWorkbook = 2

  ' Make the application visible - just toggle the setting
  xlApp.Visible = True
   
  ' Setting Sheet2 to Active sheet, and nameing it Journal_Details
  Set xlWorkSheet = xlWorkBook.Worksheets("Sheet2")

 

Thanks!
Lost & Confused Eric

 

Vbscript, Sql &amp; Write/add Record To Existing Excel File
Hi, Posted the question before - but could not get it going!

Can someone please Assist, as i need this to work it's part of my project!

VBscript, I'm executing a SQL command then with the result data append/add the row to an existing excel file.
Please anyone please Assist!

Scipt

Code:

Dim objXL
Dim File_name
Option WScript

File_name="\srv08-za063source est.xls"
Set objXL = WScript.CreateObject("Excel.Application")
Set wb = objXL.Workbooks.Add
Set ws = objXL.Worksheets(1)
r = 2
objXL.Visible = False


Set objConn = CreateObject( "ADODB.Connection")
Set resultSet = CreateObject ("ADODB.RecordSet")
objConn.Open fcCustom.Value(Process.Company, "ConnectionsFCDB")
strSQL = "select id,ProcessID from dbo.fcEventArchive where EventHistID = 2523"
resultSet.Open strSQL, objConn, 1, 2

On Error Resume Next
resultSet.MoveFirst

Do While Not resultSet.eof
ws.Cells(r, 1).Value = (resultset(0))
ws.Cells(r, 2).Value = (resultset(1))
resultSet.MoveNext
r = r + 1
Loop

resultSet.Close
objConn.Close
Set resultSet = Nothing
Set objConn = Nothing

Loading An Existing Excel To An Excel Sheet On A Form
How can I load Sheet1 of a current Excel program to a sheet object on my VB form? I don't want to load the entire app, just one sheet.

thanx.

How To Append A Text File In An Existing Excel File
Hi!

I am getting some problem in appending a text file in my existing excel file. All I want to write a Macro which will read my text file and append the fields from the text file to existing excel file.
Can someone help me???

I would really appreciate that.
Thanks.
Jit

Trouble! Importing CSV Text File To EXISTING Excel Worksheet
Hey all.. I've been searching around here for a while and there are quite a few posts on how to import text files into excel worksheets. However, none of them are working for my particular project. I've tried some code like this:

Code:
With myXLObj
        .Workbooks.OpenText FileName:=lSourceFile, DataType:=XLDELIMITED, _
            TextQualifier:=XLDOUBLEQUOTE, Comma:=True, _
            FieldInfo:=Array(1, 2)
End With


.... but that opens a new workbook and doesn't help me out. I think I should read the text file into an array using line input statements, but I cannot come up with the code. I want the text file entered into excel exactly how it is, taking up a worksheet in an existing workbook. I have attached the text file, so any help would be awesome. Thanks.

NOTE: disregard the tab delimiters on the attached file.... I just attached it to show the type of data I need imported, and I will switch the text file to CSV when I run the program.

Overwriting Excel Error
I am running a routine in Excel VBA that copies a worksheet then saves it into another work. Heres the code:


Code:
Sub SaveReport(Path, FailSuffixStr, ReportFileName)
Dim FileNameStr As String

FileNameStr = Path + "Report" + Mid(Str(Cells(1, 21)), 2, 6) + FailSuffixStr + ".xls"

Sheets("Report").Copy

ActiveWorkbook.SaveAs FileName:=FileNameStr, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="pass", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close

Windows(ReportFileName).Activate

End Sub
The issue I am having is that whenever I re-run the procedure I get an error:


Quote:




Run-time error '1004':
Operation failed. 'test.xls' is write reserved.




I see that is having a problem overwriting the file but what solution can I can use?

Help! VB Codes To Disable An Excel File Macro
Hello,
I have a VBA program which goes and opens all excel files from a specific folder and copy to one seperate workbook.
This program, however, is not able to open any file which contains any macro. Once it gets to a macro file , it does not response.
Are there any VBA codes which I can include in my program which can turn off or disable all macros in excel file residing in that specific folder.
I would really appreciate any feed back.
Thanks



Edited by - kashif8929 on 10/29/2002 10:39:37 AM

Excel Macro To Disable File Menu
Hi,

I am wondering if it would be possible to make invisible/disable drop down menu items (From File through Help) on Excel spreadsheet. It would be okay to attach a Macro to the spreadsheet if the macro does this job for me.

If you have any suggestions, I would greatly appreciate.

Thanks,
Ravi.

Saving Excel File Without Dialog Box
I am writing a program to update an excel spreadsheet when a user completes a form. I have declared the excel as workbook, application and sheet, but everytime I try to save, it shows that the file is read only and the save as dialog box opens to be saved as a new file. I have tried to make the file not read only upon opening, but still doesn't seem to work. I want to update the the current excel spreadsheet. I am able to do this by declaring excel as an object. Can I also do this when excel is declared as a workbook, application and sheet?

File Save As Dialog Box In Excel
I want to attach some code to a button in an Excel worksheet so that a user is prompted to save the file but can choose filename and filepath. I am using the following code:

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Office Workbook (*.xls), *.xls")

This code goes through the motions of opening the 'File/Save As' dialog box but when you choose location and filepath it doesn't actually save the document anywhere!

Any thoughts?

Open File Dialog Box In Excel Macro
I have a TON of delimited text files that are all exactly the same in structure to import into Excel. If I record a macro to do it, of course it includes the name of the file to open and I can only get that one file.

Here is the code in the macro:
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:Documents and Settingsglen_buckMy DocumentsAMTRUnZipped_LEAsLEA010Sch010-000.txt" _
, Destination:=Range("A1"))

How can I alter this so that I get an Open File Dialog Box and I can choose which file to open, then have the rest of the macro continue importing the data?

Thanks alot.

Problem With Save Dialog Box For An Excel File
Hi guys, I encountered a runtime error'55' (file already opened) message and I highlighted the part in yellow in the following code. This code transfers data in a file to listview and then reads the listview to an Excel table/file but the problem arises when I tried to open a dialog box to save the Excel file. Can anyone tell me how to rectify this?

Private Sub command_Click()

'Create listview for result table
Dim s() As String
Dim l As ListItem
Dim j As Integer
Dim TextFile As String
Dim iFile As Integer

iFile = FreeFile()
Open "C:file.txt" For Input As #iFile

Do While Not EOF(iFile)
Line Input #iFile, TextFile
TextFile = Trim$(TextFile)
If Len(TextFile) > 0 Then ' This ignores any blank lines in the file
Do While InStr(TextFile, " ") > 0
TextFile = Replace(TextFile, " ", " ") ' Replace all multiple spaces with a single space
Loop
s = Split(TextFile, " ")
Set l = ListView1.ListItems.Add(, , s(0)) ' Add a new row and place a value in column 1
For j = 1 To UBound(s)
l.SubItems(j) = s(j)
Next
End If
Loop

With CommonDialog1
.Filter = "Excel Files (*.xls)/*.xls" ' Set the Fileter (again the / should be a Pipe Character
.DefaultExt = ".xls" ' Set the default Extension
End With

CommonDialog1.ShowSave ' Show the SaveDialog Window

Open CommonDialog1.FileName For Input As #1 'Opens The File
Dim xwBook As Excel.Workbook, xsSheet As Excel.Worksheet
Dim lColumn As Long, lRow As Long
Dim lSubItems As Long
Set xwBook = Excel.Workbooks.Add
Set xsSheet = xwBook.Worksheets(1)

' Column Headers
For lColumn = 1 To ListView1.ColumnHeaders.Count
xsSheet.Cells(1, lColumn) = ListView1.ColumnHeaders(lColumn).Text
Next

' Listview Data
For lRow = 1 To ListView1.ListItems.Count
xsSheet.Cells(lRow + 1, 1) = ListView1.ListItems(lRow).Text
lColumn = 2

Do While lColumn <= ListView1.ColumnHeaders.Count
xsSheet.Cells(lRow + 1, lColumn) = ListView1.ListItems(lRow).SubItems(lColumn - 1)
lColumn = lColumn + 1
Loop
Next

Close #1 'Closes the file

End Sub

Problem With Save Dialog Box For An Excel File
This code transfers data in a file to listview and then reads the listview to an Excel table/file.I tried to modify the part from the opening of the SAVE dialog box till we save the excel file as the filename indicated by the user. The following is the code which I modified:

[vb]
With CommonDialog1
.Filter = "Excel Files (*.xls)/*.xls" ' Set the Filter (again the / should be a Pipe Character
.DefaultExt = ".xls" ' Set the default Extension

.ShowSave

Dim myExcel As Excel.Application
Dim xwBook As Excel.Workbook, xsSheet As Excel.Worksheet, myWorkBook
CreateObject ("Excel.Application")
Set xwBook = myExcel.Workbooks.Open(CommonDialog1.FileName)

Dim lColumn As Long, lRow As Long
Dim lSubItems As Long


' Column Headers
For lColumn = 1 To ListView1.ColumnHeaders.Count
xsSheet.Cells(1, lColumn) = ListView1.ColumnHeaders(lColumn).Text
Next

' Listview Data
For lRow = 1 To ListView1.ListItems.Count
xsSheet.Cells(lRow + 1, 1) = ListView1.ListItems(lRow).Text
lColumn = 2

Do While lColumn <= ListView1.ColumnHeaders.Count
xsSheet.Cells(lRow + 1, lColumn) = ListView1.ListItems(lRow).SubItems(lColumn - 1)
lColumn = lColumn + 1
Loop
Next

myWorkBook.Close (True)

End With[/vb]

when I run the above I got the runtime error91 with this message
'Object variable or with block bariable not set' and the line highlighted in yellow above.

Save Excel File Using Common Dialog Box
Hi all

I am trying to save the Excel file that I created with data using Common Dialog box. After I created the file the messagex box pops up with two options "Save As" and "View". So I would like be able to save the file when the user clicks "Save As". So the "Save As" dialog will pop up and then I can choose the destination and save the file. After this all books will be closed. If tthe user click To view the file, I would like to load my file with data in to excel.

Please help me with this one

thank you

Problem With Save Dialog Box For An Excel File
This code transfers data in a file to listview and then reads the listview to an Excel table/file.I tried to modify the part from the opening of the SAVE dialog box till we save the excel file as the filename indicated by the user. The following is the code which I modified:

[vb]
With CommonDialog1
.Filter = "Excel Files (*.xls)/*.xls" ' Set the Filter (again the / should be a Pipe Character
.DefaultExt = ".xls" ' Set the default Extension

.ShowSave

Dim myExcel As Excel.Application
Dim xwBook As Excel.Workbook, xsSheet As Excel.Worksheet, myWorkBook
CreateObject ("Excel.Application")
Set xwBook = myExcel.Workbooks.Open(CommonDialog1.FileName)

Dim lColumn As Long, lRow As Long
Dim lSubItems As Long


' Column Headers
For lColumn = 1 To ListView1.ColumnHeaders.Count
xsSheet.Cells(1, lColumn) = ListView1.ColumnHeaders(lColumn).Text
Next

' Listview Data
For lRow = 1 To ListView1.ListItems.Count
xsSheet.Cells(lRow + 1, 1) = ListView1.ListItems(lRow).Text
lColumn = 2

Do While lColumn <= ListView1.ColumnHeaders.Count
xsSheet.Cells(lRow + 1, lColumn) = ListView1.ListItems(lRow).SubItems(lColumn - 1)
lColumn = lColumn + 1
Loop
Next

myWorkBook.Close (True)

End With[/vb]

when I run the above I got the runtime error91 with this message
'Object variable or with block bariable not set' and the line highlighted in yellow above.

Problem With Save Dialog Box For An Excel File
Hi guys, I encountered a runtime error'55' (file already opened) message and I highlighted the part in yellow in the following code. This code transfers data in a file to listview and then reads the listview to an Excel table/file but the problem arises when I tried to open a dialog box to save the Excel file. Can anyone tell me how to rectify this?

Private Sub command_Click()

    'Create listview for result table
    Dim s() As String
    Dim l As ListItem
    Dim j As Integer
    Dim TextFile As String
    Dim iFile As Integer

    iFile = FreeFile()
    Open "C:file.txt" For Input As #iFile

    Do While Not EOF(iFile)
        Line Input #iFile, TextFile
        TextFile = Trim$(TextFile)
        If Len(TextFile) > 0 Then ' This ignores any blank lines in the file
            Do While InStr(TextFile, " ") > 0
                TextFile = Replace(TextFile, " ", " ") ' Replace all multiple spaces with a single space
            Loop
            s = Split(TextFile, " ")
            Set l = ListView1.ListItems.Add(, , s(0)) ' Add a new row and place a value in column 1
            For j = 1 To UBound(s)
                l.SubItems(j) = s(j)
            Next
        End If
    Loop

With CommonDialog1
   .Filter = "Excel Files (*.xls)/*.xls" ' Set the Fileter (again the / should be a Pipe Character
   .DefaultExt = ".xls" ' Set the default Extension
End With

   CommonDialog1.ShowSave ' Show the SaveDialog Window
   
Open CommonDialog1.FileName For Input As #1 'Opens The File
Dim xwBook As Excel.Workbook, xsSheet As Excel.Worksheet
  Dim lColumn As Long, lRow As Long
  Dim lSubItems As Long
  Set xwBook = Excel.Workbooks.Add
  Set xsSheet = xwBook.Worksheets(1)
   
  ' Column Headers
  For lColumn = 1 To ListView1.ColumnHeaders.Count
    xsSheet.Cells(1, lColumn) = ListView1.ColumnHeaders(lColumn).Text
  Next

  ' Listview Data
  For lRow = 1 To ListView1.ListItems.Count
    xsSheet.Cells(lRow + 1, 1) = ListView1.ListItems(lRow).Text
    lColumn = 2

    Do While lColumn <= ListView1.ColumnHeaders.Count
      xsSheet.Cells(lRow + 1, lColumn) = ListView1.ListItems(lRow).SubItems(lColumn - 1)
      lColumn = lColumn + 1
    Loop
Next

Close #1 'Closes the file

End Sub

Problem With Save Dialog Box For An Excel File
Hi guys, I encountered a runtime error'55' (file already opened) message and this line was highlighted in yellow 'Open CommonDialog1.FileName For Input As #1 'Opens The File'. This code transfers data in a file to listview and then reads the listview to an Excel table/file but the problem arises when I tried to open a dialog box to save the Excel file. Can anyone tell me how to rectify this?

Private Sub command_Click()

'Create listview for result table
Dim s() As String
Dim l As ListItem
Dim j As Integer
Dim TextFile As String
Dim iFile As Integer

iFile = FreeFile()
Open "C:file.txt" For Input As #iFile

Do While Not EOF(iFile)
Line Input #iFile, TextFile
TextFile = Trim$(TextFile)
If Len(TextFile) > 0 Then ' This ignores any blank lines in the file
Do While InStr(TextFile, " ") > 0
TextFile = Replace(TextFile, " ", " ") ' Replace all multiple spaces with a single space
Loop
s = Split(TextFile, " ")
Set l = ListView1.ListItems.Add(, , s(0)) ' Add a new row and place a value in column 1
For j = 1 To UBound(s)
l.SubItems(j) = s(j)
Next
End If
Loop

With CommonDialog1
.Filter = "Excel Files (*.xls)/*.xls" ' Set the Fileter (again the / should be a Pipe Character
.DefaultExt = ".xls" ' Set the default Extension
End With

CommonDialog1.ShowSave ' Show the SaveDialog Window

Open CommonDialog1.FileName For Input As #1 'Opens The File
Dim xwBook As Excel.Workbook, xsSheet As Excel.Worksheet
Dim lColumn As Long, lRow As Long
Dim lSubItems As Long
Set xwBook = Excel.Workbooks.Add
Set xsSheet = xwBook.Worksheets(1)

' Column Headers
For lColumn = 1 To ListView1.ColumnHeaders.Count
xsSheet.Cells(1, lColumn) = ListView1.ColumnHeaders(lColumn).Text
Next

' Listview Data
For lRow = 1 To ListView1.ListItems.Count
xsSheet.Cells(lRow + 1, 1) = ListView1.ListItems(lRow).Text
lColumn = 2

Do While lColumn <= ListView1.ColumnHeaders.Count
xsSheet.Cells(lRow + 1, lColumn) = ListView1.ListItems(lRow).SubItems(lColumn - 1)
lColumn = lColumn + 1
Loop
Next

Close #1 'Closes the file

End Sub

Add New Text To A Flexgrid Cell Without Overwriting Existing Values
Hi,

I have a flexgrid which contains existing values in each cell. I want to be able to add new text to each cell without losing these initial values. I have tried a number of approaches, but all overwrite the existing text. Can anyone tell me if this is at all possible, and if yes, how I would go about it?

Open File Dialog In Excel Through Visual Basic
Hello... I'm trying to find the code to do the following.

public sub
Dim strMyFile as String
strMyFile = (file opener dialog)
(rest of code)
end sub

But I'm having trouble finding the appropriate dialog menu command... can you point me in the right direction?

Using &"save Copy As&" In Excel To Overright Existing File
Hello, some basic info first.

I have a workbook which, has the..

worksheets
workbook
and vba properties (locked for viewing)

all password protected, now in excel 2000, you cant lock
all of these and still save in the '.xls' format (or at least not after the initial first save)

so i have used the "savecopyas" function, and all works ok
until it encounters a file of the same name, I want it to overwrite it and it wont

can you guys look at my code and tell me what i need to add to get it to overwrite the existing file by default....or delete it first, as this is driving me up the wall and i must get it finished soon !! or will go mad

code ...

Private Sub savebook_Click()
Dim AlsWa As String
AlsWa = Sheets("1909").Range("C18").Text
Application.DisplayAlerts = False
MousePointer = vbHourGlass
ActiveWorkbook.SaveCopyAs Filename:=ThisWorkbook.Path & "" & "1909B " & AlsWa & ".xls"
Application.DisplayAlerts = True
End Sub

cheers

How To Let Common Dialog Check Existing File?
I am using code to save a file:

commondialog1.ShowSave
Open commondialog1.FileName For Output As #1
Write #1, "abcd"
Close #1

But the commondialog1 never ask me whether that file is already existed, just directly replace the old one, how could I let it pop up the very useful msgbox to ask " the file is already existed, would you like to replace it?", thx for any help.

Also what should I set my code if the user click Cancel button on the commondialog1 (how could I know)?

yanli

Using Existing Excel Template
Hello I have documentation that teaches you how to make my VB program open up Excel and enter data in the cells, but I want to use an existing template and have the cells 'fill up' with the data from the program. If anyone knows please help me and thank you in advance.

Use An Existing Instance Of Excel
Hi guys!

I have a problem here. I am exporting the contents of an ADO recorset to an Excel Sheet, and I want to know if there is a way of using an existing excel window wich is already opened to create the new sheet or is it really necessary to create a new instance of the Excel.Application object.

Thank you in advance

Copyright © 2005-08 www.BigResource.com, All rights reserved