Export To Excel File Format
I have a VB6.0 application, and I want to save a file in say latest Excel format, where such a file will have whatever strings etc I want to insert into whatever cells.
I don't want to object-link to an Excel app since Excel is not necessarily installed. I simply want the file to be able to be opened by any Excel reading application.
I would be very grateful for pointers to the first steps involved in such a process - presumably MS publish an API that will allow me to write such files?
Many thanks for any response,
Mike
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Export A File In EXCEL Format
Hi
I'm a VB programmer and I ussaly access my files through ODBC driver using ADODB recordset and SQL statements. I want to export the result of thew sql request in Excel format. How can I do it ?
Helping me = Saving me
Thanx
CU
Failed To Export The Report, Error In MS Excel Export Format DLL
Hi,
I have a little problem with Crystal Reports (CR 8) :
I try to export one of my reports from a VB program. When I try to export it in "Comma Separated Values" format everything is ok (except this is not the format I need
). When I try to export it in excel format it works, on some PCs but not on others ?
VB code sample :
rpt.ExportOptions.FormatType = crEFTExcel80Tabular
rpt.ExportOptions.DestinationType = crEDTDiskFile
rpt.ExportOptions.UseReportDateFormat = True
rpt.ExportOptions.UseReportNumberFormat = True
rpt.ExportOptions.DiskFileName = txtExportDirectory.Text & ExportName
rpt.Export False
I tried all the excel formats (crEFTExcel50, crEFTExcel50Tabular, crEFTExcel70 ...) but on some PC's I always get the "Exporting Records" window for a second and then the error "Failed to export the report, Error in MS Excel Export Format DLL".
Can anyone help me out ?
Thanks !
Export Excel Chart To Wmf Format
there is method in excel VBA : chart.export to export chart in worksheet to GIF, JPG or BMP (bitmap format), is it possible to export to wmf format (vector format) (or is there any way to export excel chart to wmf programatically ) ?
thanks in advance
rd
To Export Crystal Report In Excel Format
Hello friends,
I made an application in vb 6 to generate crystal reports but i don't want to use export button of crystal report and want to add my code in vb which will automatically convert it into excel or pdf format. Also if possible it shld automatically send mail to my id without using outlook express....
waiting for responses,
varsha
Export SQL Compact Server Data To Excel Format
Hi,
I am interested in created a button, or menu item that allows a user to export currently displayed record from the database. Can someone scope this for me? Is this a complicated bit? I really appreciate any advice. As usual - thank you in advance.
Mike
Export File Format
What do I need to do so the output of the script below is in fixed width/space delimited and without the column headings?
Thanks in advance!
This is what I have so far:
'------------------------------------------------------------
' export
'
'------------------------------------------------------------
Function export()
On Error GoTo export_Err
Beep
MsgBox "File NAVYUDL.txt will be exported to c: emp.", vbInformation, ""
DoCmd.OutputTo acTable, "OEL DATA", "MS-DOSText(*.txt)", "c: empNAVYUDL.txt", False, ""
export_Exit:
Exit Function
export_Err:
MsgBox Error$
Resume export_Exit
End Function
Converting To Date Format Before Export File
Hi there. I am pretty new to Visual Basic but I managed to get my code working somehow but not perfectly done. I'm supposed to export data from a DataGrid to Excel file. It appears that items under Date column is printed out as a 'general' format instead of date. (My 6/12/2001 has turned into 37057 or something..) Is there a code to convert that particular column to Date format when exporting the data? Thanks in advance for your help.
Export Access Table To CSV Format File
Good Evening All, I'm hoping to pick your collective VB brain ...
I'm not a VB programmer (not really any kind of programmer), and i'm not a database developer -- but I'm working on a project with someone who is both of those things.
Recently he was asked to write a script which would inter alia dump a small four-column table from MS Access to a data file in CSV format. To cut a short story even shorter, he can't do it. I think the probable reason is that he does not know what CSV format is, but I want to be fair -- perhaps it is not as easy as I supposed, and instead of complaining I could just point him in the right direction.
So here are my questions:Is there a VB module which can be used to export an Access table into syntactically-correct CSV format?Is it in any way problematic?
Thanks for your attention
Export A Crystal Report To PDF File Format Through VB
Hi,
when i export a crystal report to PDF file format through VB i did not get a file PDF file format . but if i goto crystal report and from the preview i can export PDF file format. Can you guys pls give me a suggestion ,
Thanks,
kirupaj
How To Write A Csp/VB For CE8.0 To Export To Diff File Format?
Hi everybody,
I am new to CE.... Is there a place where sample code can be found for writing a CSP to export scheduled rpts to diff file formats??
Can I auume that the following is relevant...
cr.Destination
cr.PrintFileName
cr.PrintFileType
If so where can I find the syntax for using them? Many thanks.
best regards
Tony
How To Know The User Selection Export File Format Based On Microsoft Data Report
Hi all,
i am using microsoft data report in VB 6 , how to i get the user selection file format (open the export file dialog box) he or she wish to export the file for example to EXCEL. or is any window API to detect the file selection from the file dialog box.
after when the user select *.xls for export i wish to do other application processing , therefore is there any other to detect or know the file format selection from the file dialog box ?
Private Sub DataReport_Initialize()
rptDemo.ExportFormats.Add _
Key:="ExcelReport", _
FormatType:=rptFmtText, _
FileFormatString:="Excel (*.xls)", _
FileFilter:="*.xls"
End Sub
Private Sub DataReport_AsyncProgress(ByVal JobType As MSDataReportLib.AsyncTypeConstants, ByVal Cookie As Long, ByVal PageCompleted As Long, ByVal TotalPages As Long)
Dim iCount As Integer
Dim i As Integer
'iCount = rptDemo.ExportFormats.Count
'
'For i = 1 To iCount
' Debug.Print rptDemo.ExportFormats(i).FileFormatString
'Next
End Sub
Exporting An Access Table To A New Excel SS And Open Excel File Afetr Export
Hi,
Can some help me with writing the code to Export a table from Acces into a new excel spreadsheet, then open an instance of excel to view the newly exported table? I can get the file to export but I am having trouble with the code to open Excel, thanks for you help. See code below..
Private Sub Command26_Click()
DoCmd.TransferSpreadsheet transfertype:=acExport, _
tablename:="newreqtable", _
FileName:="F:UserdataSelectionData.xls", hasfieldnames:=True, _
Spreadsheettype:=8
MsgBox "Export is Complete", vbOKOnly, "Selection Data Export"
MsgBox "Your new file is located on F:UserdataSelectionData.xls", vbOKOnly, "Data Export Location"
'This is where I need to have excel open the file for the client.
End Sub
Export Excel Sheet To .csv File Using VB6
I can't seem to find what I'm looking for. I'm attempting to read an xcel file and output a sheet to a .csv file. The code I'm using is SLOWER than molasses in january. It takes a second for each row to be processed. There has got to be a faster way. Not fancy, but I'm attempting to create the .csv file before finishing it up.
Here's my code:
Public Function ProcessXLS(ByVal srcFile As String, ByVal destFile As String, _
Optional AppendData As Boolean, Optional StrDelimiter As String)
Dim wkbObj As Object, ActSht As Integer, FNum As Integer
Dim BeginRow As Integer, BeginCol As Integer, strCellData As String
Dim EndRow As Integer, EndCol As Integer, i As Integer, StrDelim As String
Dim RowIdx As Integer, strDataLine As String, ColIdx As Integer
If StrDelimiter = "" Then StrDelimiter = "," 'default
LogMsg "Entering XLS for " & srcFile, True
On Error GoTo ERR_XLS
Set wkbObj = CreateObject("Excel.Application")
'open the excel spreadsheet
wkbObj.workbooks.Open srcFile, True
'==========================================
'get the name of all sheets in the workbook
Dim wsNames(100)
Dim wsNamesCnt As Integer
wsNamesCnt = 0
'just seeing what's available.
For Each sh In wkbObj.activeWorkbook.Sheets
wsNames(wsNamesCnt) = sh.Name
wsNamesCnt = wsNamesCnt + 1
Next sh
'active sheet???
ActSht = 0
'open output file
destFile = destFile & Trim(wsNames(ActSht)) & ".CSV"
FNum = FreeFile
If AppendData Then
Open destFile For Append Access Write As #FNum
Else
Open destFile For Output Access Write As #FNum
End If
With wkbObj.ActiveSheet.UsedRange
BeginRow = wkbObj.ActiveSheet.UsedRange.cells(1).Row
BeginCol = wkbObj.ActiveSheet.UsedRange.cells(1).Column
EndRow = wkbObj.ActiveSheet.UsedRange.cells(wkbObj.ActiveSheet.UsedRange.cells. Count).Row
EndCol = wkbObj.ActiveSheet.UsedRange.cells(wkbObj.ActiveSheet.UsedRange.cells. Count).Column
End With
For RowIdx = BeginRow To EndRow
StrDelim = ""
strDataLine = ""
Debug.Print Time
For ColIdx = BeginCol To EndCol
strDataLine = strDataLine & StrDelim & Chr$(34) & strCellData & Chr$(34)
' strDataLine = strDataLine & StrDelim & strCellData
StrDelim = StrDelimiter
Next ColIdx
Debug.Print Time
Print #FNum, strDataLine
Debug.Print Time
DoEvents
Next RowIdx
'==========================================
wkbObj.workbooks.Close
wkbObj.Quit
Set wkbObj = Nothing
END_XLS:
ProcessXLS = strRetCode
LogMsg "Exiting XLS", True
Exit Function
ERR_XLS:
strRetCode = Error(Err.Number) & " " & Err.Description
If IsObject(wkbObj) Then
If wkbObj.Name = "Microsoft Excel" Then
LogMsg ("Error XLS: " & strRetCode)
wkbObj.workbooks.Close
wkbObj.Quit
End If
End If
Resume END_XLS
End Function
Any help is appreciated.
Thanks
Matt.
XML Namespaces In Excel Export File
Hi!
After saving a spreadsheet in xml format, I want to navigate in the generated domdocument and create a new xml...well I´m trying to do so applying a xsl, but I´m having some troubles in parsing namespaces!!How can I remove then?!Should I?!Where i´m I!?
Does anyone have done something like that?!
Many thanks!!
Export Results To An Excel File
Given a recordset (originating from an SQL Query call) which is used to populate a datagrid (as datasource). How could I allow the user to “export” the results into an Excel [.csv I assume] file?
Of course it must maintain all the columns and rows.
Export To Excel File Type
Can anyone help us out please.......
We want to save a file as an Excel File Format. The problem
we have here is we don't have MS Excel installed. Without
having Excel we would like to create a File that is of Excel File Type. So that we can open this file directly using some other spreadsheet application (f.i. StarOffice Suite).
Thanks in advance.
Regards
Murali
Export Results To An Excel File
Given a recordset (originating from an SQL Query call) which is used to populate a datagrid (as datasource). How could I allow the user to “export” the results into an Excel [.csv I assume] file?
Of course it must maintain all the columns and rows.
Export Data To An Excel File Using VBA?
Hi,
I have a MS Access DB that produces reports based on a SQL statement that is built in code. So, once the SQL statement is built it can look something like this (when variable is printed into imediate window):
SELECT * FROM MyTable WHERE (Company = 2) AND (WorkStatus = 'IT Manager')
I then use this SQL statement to open a report (using DoCmd.OpenReport "Query_Download_Report", acViewPreview, , SQL) but now the user wants the data to be sent straight to an excel file - much like it does when the user manually exports report data to .xls file but all done within code. Can anyone help me out with this one?
Thanks
IZM
Export Text File To Excel
I need to create a program that will take data from a text file and export it into an Excel document. The data in the text file is in already in a columnar format, so I'm thinking I need to "grab" each column as a seperate entity in VB and export the columns into excel. I was thinking an array may be the way to go? Also, I would like to be able to delete the text file after the data has been read from it. Any ideas to get me started would be very much appreciated!!
Export Data Into Excel From A VB File
I have a file that I need to extract data from but the file seems to be unreadable. It appears it is a VB file that is a fixed length file. I have tried renaming the file with an xls extension and importing as fixed length and that does not work.
Please help as the vendor of the file is not helpful AT ALL.
Help With SQL To Excel Export File Saving Errors
I have a sub routine that will export the contents of a SQL table to an Excel workbook. It is called when the Save File As button is clicked, which opens a Save common dialog so the user can enter the path and filename. This is stored in the FileToSave variable which is passed into the subroutine along with the SQL query string
Then, the routine opens objects and connections for ADO SQL, gets the recordset, and then opens objects for Excel automation. Then it writes the contents of the recordset row by row to the excel sheet. All fine and dandy. However, problems start happening at the code line that actually saves the file when exporting is finished.
The first error I had occurs when the user chooses a filename that already exists AND that file is currently open. It would cause a run-time error '1004' Cannot Access file. So I added the 'On Error goto OpenFileError' handler that displays a message to close the file, then click OK, and it would resume at the save file line again.
So the user closes the file and the line doesnt cause an error the second time through, but an Excel prompt comes up: 'File already exists. Would you like to overwrite?' with 'Yes, No, or Cancel' buttons. Also, this excel prompt is displayed when the user selects an exisiting filename and the file IS NOT currently open.
Now, the routine works with desired reuslts when the user chooses the 'Yes' overwrite button, but selecting 'No' or 'Cancel' causes the Excel prompt to go away and then first message 'File in use, close the Excel file, and click OK' reappears. The file isnt really open this time, the program is just interpretting this MS Excel Conflict Resolution dialog as another error(I think), so it jumps to the error handling code again. So an endless loop will occur unless the user chooses to overwrite.
So, is there a way using the Save Common Dialog box upon the user choosing a path and filename to check if the file already exists, and prompts the user to overwrite at that moment, before passing the filename to the export routine? Then i could set the xlapp.Workbooks.SaveAs code to 'always overwrite', thus getting rid of having excel worry about this conflict, thus removing this second error prompt(which i cant find a way to address through code). Any other suggestions to a better way of doin this are greatly appreciated.
Code:
'Button Code to save Master File
Private Sub buttonSaveMaster_Click()
Dim xlFileName As String
Dim SQL As String
diSave.DialogTitle = "Save"
diSave.Filter = "Excel Files (*.xls)|*.xls"
diSave.ShowSave
xlFileName = diSave.FileName
SQL = "select * from Master"
Call CreateSpreadsheetFromSQL(SQL, xlFileName)
End Sub
Code:
'Routine to Save Master File
Public Sub CreateSpreadsheetFromSQL(strSQL As String, FileToSave As String)
Dim xlApp As Object
Dim xlSheet As Object
Dim xlRng As Object
Dim rs As Object
Dim dbConn As ADODB.Connection
Dim stConn As String
Dim vntRecords As Variant
Dim vntRecordsTemp As Variant
Dim cntRows As Long, cntFields As Long
'Set up the recordset
Set dbConn = New ADODB.Connection
stConn = "[ADOCONNECTIONSTRING]"
dbConn.Open stConn
Set rs = CreateObject("ADODB.Recordset")
rs.Open strSQL, dbConn
vntRecords = rs.GetRows
'Set up the Excel objects
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Add
Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
'Fill in the spreadsheet
Lots of Excel automation code here...
Retry:
On Error GoTo OpenFileError
xlApp.Workbooks(1).SaveAs FileToSave, xlNormal '<---this is error line
xlApp.Quit
Set xlApp = Nothing
Set xlRng = Nothing
Set xlSheet = Nothing
If Not (rs Is Nothing) Then rs.Close
Set rs = Nothing
Exit Sub
OpenFileError:
MsgBox "File is currently open. Please close Excel before clicking OK.", vbExclamation, "Error"
Resume Retry
End Sub
Getting An Error Trying Export Data To An Excel File Using VBA.
Code:
Public Function ExportRequest() As String
On Error GoTo err_Handler
' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer
Const cTabTwo As Byte = 2
Const cStartRow As Byte = 4
Const cStartColumn As Byte = 3
DoCmd.Hourglass True
' set to break on all errors
Application.SetOption "Error Trapping", 0
' start with a clean file built from the template file
sTemplate = CurrentProject.Path & "SalesTemplate.xls"
sOutput = CurrentProject.Path & "SalesOutput.xls"
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput
' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabTwo)
' Commented out due to using a query instead
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(qryPublicUse, dbOpenSnapshot) ' Used qryPublicUse instead of sSQL
' For this template, the data must be placed on the 4th row, third column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow
If Not rst.BOF Then rst.MoveFirst
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
Me.lblMsg.Caption = "Exporting record #" & lRecords & " to SalesOutput.xls"
Me.Repaint
For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)
If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If
wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next
wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop
ExportRequest = "Total of " & lRecords & " rows processed."
Me.lblMsg.Caption = "Total of " & lRecords & " rows processed."
exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function
err_Handler:
ExportRequest = Err.Description
Me.lblMsg.Caption = Err.Description
Resume exit_Here
End Function
It keeps hanging up on me at the
Code:
Me.lblMsg.Caption = "Exporting record #" & lRecords & " to SalesOutput.xls"
part. It tells me that the .lblMsg Method or Data member was not found. Any thoughts?
Export From One Excel File To Open Spreadsheet
Hello there,
I will try and explain my problem the best I can and wonder if someone out there can help me with it !!!!!
I want to be able to set up a macro so that it allows the user to export data from an existing saved excel spreadsheet to one that the user has already open.
I dont want to use copy and paste as i want to have the facility available for the user to open any file he wants. Of course all the files that he can open are in the same format - but may contain differnet data.
So is it possible for some type dialog box to open, the user selecting the appropriate file and the data flowing through to the excel spreadsheet such that the data in a cell in the saved s/sheet goes specifically into cell A1 (for instance) of the currently opened s/sheet.
I am a newbie, but this sounds awfully complicated - can it be done??
Cheers
Export SQL Table Details To An Excel File
Hi,
Can anyone show me some VB6 code (or an easier way) to drill into a SQL Server Table and Export to Excel a listing of:-
- Field Name
- Data Type
- Size
- Nulls
- Default Entry
- etc.
This would save me heaps of time in transfering Data between tables as I can see more information about fields than just the Table Views.
Thanks & Regards
Ian Cairns
:-)
Export From A Text File To An EXCEL Document And The Other Way Around!!!
Dear All,
I have a comma separated txt file, what i want to do is to export it in an excel, can any one show me the way??? I know that i can directly import it into an excel file, just opening it with excel program,but i want to add the specific function in my program.
I would really appreciate also if u can tell how can i read from an excel file and create a text file?
Thanks in advacnce!!!!
Crystal Report 8. Export To Excel File
Hi! Good Morning!
I am Tan.
I am using crystal report 8.
In the icon like envelop. I can't export out the report in Ms Excel. I can export to PDF, text and etc. Just cant found the Ms Excel.
Same program, i have install to 2 PCs. Only one of the pc facing this problem. What should i do now?
When i install the program, i also copy
c:winDOWSsystem32
egsvr32 crviewer.dll
c:winDOWSsystem32
egsvr32 Crystl32.OCX
c:winDOWSsystem32
egsvr32 crpe32.dll
c:winDOWSsystem32
egsvr32 craxddrt.dll
c:winDOWSsystem32
egsvr32 craxdrt.dll
c:winDOWSsystem32
egsvr32 craxdui.dll
c:winDOWSsystem32
egsvr32 cpeaut32.dll
to the 2 PCs.
Please help me!
THANKS!
p/s : I am very sorry that i have to post in classic visual basic. Because not reply in crystal forum.
How To Export Query Result To Excel File ?
I am come from Hong Kong.
Recently, I am writing vb scripts to export query result to a excel file.
However, I don't know how to do so, can anyone teach me ?
Thanks for your kindly help
Export Excel Data Into A Formatted Text File
Hi all,
I haven't done any programming yet with VBA or EXCEl. I wanted to export data from one application to another application and their database stucture is incompatible. I exported the tables into a excelsheet and now wanted to parse the data from the excel sheet and create a formatted text output into a text file.
For instance the data in the excel table is in the following format;
Firstname;Lastname;Address1;Address2;PIN;Place;Country Code;Area Code;Telephone;TeleFax
Martin;Fritz;Palmstreet 45;Second cross;22334;Someplace;45; 07 11;98 7248 927;928 73 49
and I have to parse this table and create a text file like this;
SetField($connID, "Firstname", "Martin");
SetField($connID, "Lastname", "Fritz");
SetField($connID, "Address1", "Palmstreet 45");
SetField($connID, "Address2", "Second cross");
SetField($connID, "PIN", "22334");
SetField($connID, "Place", "Someplace");
SetField($connID, "Country Code", "45");
SetField($connID, "Area Code", "711");
SetField($connID, "Telephone", "987248927");
SetField($connID, "Telefax", "9287349");
This is just an example. The original file has more than 1500 rows and its cannot be done by hand. Note that in the table, the column entries has unwanted spaces in between phone numbers or text and they should be removed and inserted in the text file.
Once I transfer the excel table into this text format i have a tool that will export and create the necessary datastucture in the new application. Since I haven't worked with VB or Excel, I am not able to do it and I don't want to learn it from scratch.
Any help would be greatly appreciated.
Regards,
Shafas
How To Export Crystal Report To An Word And Excel File
hi Everyone,
does anybody know how to export a crystal report to an word(.doc) or excel(.xls) file.
This is really important to me b`cos i have all the reports in crystal reports and i have to mail them, but i couldn`t do this because they are all in crystal report format.
but if I just can export this reports to a word or excel file then i will be able to mail them..
I really appreciate it if anybody can show me how to do this or give any URL relevant to this...
thanx in advance,
niroshan
How Do I Export A Range Of Excel Cells As A Text File?
Greetings,
I found this much on another site:
Code:
Worksheets(1).Copy
Activeworkbook.SaveAs FileName:="file1.txt", FileFormat:=xlTextMSDOS
Activeworkbook.close false
and it works, but it saves the entire worksheet. How can I save a specific range of cells only -- as a text file?
Thanks!
DM
Insert Data In A Text File Created By Excel And Export Them All
Hi for all
This is very hard.
I want to insert into a text file create by Excel some information like name, address and so on.
The text file is saved and opened for data inserts.
The file is opened, excel´s macro waits 'till I save the file.
Then, it copy each line of the text file and put in a excel´s row.
Please HELP MEEEEEEE!!
Hughes for all
Alex
Trying To Export Data Form Access To An Excel File, Getting Permission Denied Error
Basically, I have this application that Gets data from a table using a query, then it uses VBA to Export that data to an Excel spreadsheet.
Previously, this had worked perfectly before, but I had to make changes to it to seperate the data into two different groups based on a status of either OT or OTA.
So I moved the function from the main form to a pop-up form that has a combo box on it for the user to select a status type. I also changed the query to check that combo box to get the criteria for the query.
Now when I tried to export the data, it gives me a "Run Time Error 70: Permission Denied".
I have tried this with the application running on the server(Both before and after the changes) and on my PC(Which I am the admin of), and I get the same error. Here is the export code:
Code:
Private Sub cmdExport_Click()
Dim Response As Integer
Response = MsgBox("Do you want to Export all of the " & cboStatus _
& " Licensee data to the Excel file PublicUseOutput.xls? Click <Yes> to Continue or <No> to cancel the request", _
vbQuestion + vbDefaultButton2 + vbYesNo, "Export Licensee Data")
If Response = vbYes Then
ExportRequest
Else
MsgBox "Export Request Cancelled", vbInformation, "Export Cancelled"
End If
End Sub
Public Function ExportRequest() As String
On Error GoTo err_Handler
' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer
Const cTabTwo As Byte = 2
Const cStartRow As Byte = 4
Const cStartColumn As Byte = 3
'DoCmd.Hourglass True
' set to break on all errors
Application.SetOption "Error Trapping", 0
' start with a clean file built from the template file
sTemplate = DLookup("TemplateLocation", "tblExcelLookUp")
sOutput = DLookup("PULocation", "tblExcelLookUp")
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput
' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabTwo)
'sSQL = "SELECT tblImport.*, IIf([MailPrefence]='Personal',
'[MailingAddress],[EmpAddress]) AS PrefAddr, IIf([MailPrefence]
'='Personal',[City],[EmpCity]) AS PrefCity, IIf([MailPrefence]
'='Personal',[ST],[EmpST]) AS PrefST, IIf([MailPrefence]='Personal',
'[ZipCode],[EmpZipCode]) AS PrefZip, [FirstName] & " " & [Middle]
'& " " & [LastName] AS FullName FROM tblImport WHERE
'(((tblImport.MailPrefence)<>"none") AND ((tblImport.LicenseStatus)
'="Active"));" ' Commented out due to using a query instead
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryPublicUseExport", dbOpenSnapshot) ' Used qryPublicUse instead of sSQL
' For this template, the data must be placed on the 4th row, third column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow
If Not rst.BOF Then rst.MoveFirst
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
Me.lblMsg.Caption = "Exporting record #" & lRecords & "!"
Me.Repaint
For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)
If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If
wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next
wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop
DoCmd.Hourglass False
ExportRequest = "Total of " & lRecords & " records processed."
Me.lblMsg.Caption = "Total of " & lRecords & " records processed."
exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function
err_Handler:
DoCmd.Hourglass False
ExportRequest = Err.Description
Me.lblMsg.Caption = Err.Description
Resume exit_Here
End Function
Any thoughts?
Excel File Format?
Hi,
I'm an old hand at writing to Excel files from VB6 using OLE, but it's so SLOWWWWWWW. I was just wondering, is it not possible to write directly to a file that in the same format that Excel uses? Surely that would be a million times faster (and probably a million times more complicated, but complicated is good, no? ;o)
Thanks!
Date Format In Excel File
Hi,
I'm creating an Excel file in VB by opening a CSV file and moving some columns and stuff around... you know, to pretty things up. My problem is this. If I open the CSV file in excel manually I get the wizard to help me convert the file to the proper format. I select comma as the delimiter, leave all columns in general data format. This works fine.
If I do what should (in theory) be the same operation in my VB code the dates come out as american dates in the one column that has dates in it. Dates that can't be interpreted as american dates (like 29/05/06... 29 being the month) are not imported as dates at all.
This is how I open the CSV file:
VB Code:
oExcel.Workbooks.OpenText FileName:=CSVFileName, DataType:=xlDelimited, textqualifier:=xlTextQualifierNone, _ COMMA:=True, fieldinfo:=Array(4, xlTextFormat)
This should (as I understand it anyway) open the csv file, interpret it as a delimited file with comma as the delimiter, expect no quotes for text and import the D (4th) column as straight text. It still does the same thing with the dates though.
Convert Excel File To Csv Format
Hello VB gurus,
I have unique problem with saving excel file in CSV format. I want to do in batch process.
Steps : Read excel file as input and create another csv format file.
Please help me wth my problem
Thanks in Advance
Raj
Format Data In Excel File
I do data export to excel file.
I need to write text on two different lines, but in ine row
for example : Service
Company AP #
I tried this code:
Set objRange = xlBook.Application.Cells(1, 2)
objRange.FormulaR1C1 = "Service Company AP#"
objRange.WrapText
objRange.Font.Bold = True
It gives error.
Thanks.
How To Save Excel File In Csv Format
Hello,
I have written VB program to process one excel file. the main program will create new workbook (xboo2 workbook) with additional columns based on the input excel file,
after the completion of new excel workbook (xbook2.xls). I would like save as inputfile.csv and the output file should be in .CSV format.
For example : my input file is "2005 11 sales_data.xls"
output file should be named as "2005 11 sales_data.csv"
xboo2.xls should be save as "2005 11 sales_data.csv"
So that i can load the data using loader program.
Please help me
Thanks
|
TRACKER
