Writng Data In Excel File From Vb Code
Hi
There is a problem and i want your help. I have created a file with .xls extention and also i have written some data in that file using Visual Basic 6.0 like this
Set abc = CreateObject("Scripting.FileSystemObject")
Set xyz = abc.CreateTextFile(path & strDate & ".xls", True)
xyz.Write "Serial No" vbtab "Name" .......
xyz.close
Now problem is that when i open the created file from MS Excel then a message occurs that .xls file will open in Excel but the format of the file will no be Excel format. So i save that file againg in Excel format.
I want a solution.. that how can i create Excel File from Visual Basic.
Please Help me..
Thanks
From: Tsw
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Writng Code For Run-time Activated Controls
Hey folks,
I've run accross a problem that i thought that you folks could help me with.
I'm creating controls and placing them on a form at run time. Start witha blank form then on the load event of that form running a function that adds the labels , txtboxes , cmd buttons etc to the form. This is fine I can do this.
I'm wondering how I reference these objects within my code at run time. When itry to reference them in my code before they are created it does not pick up on the new objects.
Does anybody know any good URL's that can help with the code for this:
Public Function LoadControl(oForm As Object, CtlType As String, CtlName As String) As Object
'declare object variable
Dim oCtl As Object
On Error Resume Next
If IsObject(oForm.Controls) Then
Set oCtl = oForm.Controls.Add(CtlType, CtlName)
If Not oCtl Is Nothing Then Set LoadControl = oCtl
End If
End Function
'thanks in advance
VB File Acces, Reading And Writng, EOF Implementation
Hi
I'm writing a program that would take something from a file, procces the input and write to a file ( ).
Well, I would have some quiestions(I did this kind of program before, only I don't have ths source-code):
(I will not use fso because I don't know conditions such as Eof)
1.
how do I open the file for reading
2. how do I open another file for writing
3. How do I read from the first file line by line untill I reach the end of file
4. How do I write some var. in the second file, line by line.
Thanks!
Wriitng Data In Excel File From Vb Code
Hi
There is a problem and i want your help. I have created a file with .xls extention and also i have written some data in that file using Visual Basic 6.0 like this
Set abc = CreateObject("Scripting.FileSystemObject")
Set xyz = abc.CreateTextFile(path & strDate & ".xls", True)
xyz.Write "Serial No" vbtab "Name" .......
xyz.close
Now problem is that when i open the created file from MS Excel then a message occurs that .xls file will open in Excel but the format of the file will no be Excel format. So i save that file againg in Excel format.
I want a solution.. that how can i create Excel File from Visual Basic.
Please Help me..
Thanks
From: Tsw
Excel - How To Get Formatted Data, Not Original Data Through Code
Hi all,
I am reading through an excel column, and I need to get a number from each cell. However, I don't want the original number i.e. 65.382745, I want the number the user has specified by defining the number of decimal places i.e. 2 places 65.38, 3 places 65.383.
I am currently using the sheetname.cells(row,col) property to get at the data, how do I get the formatted data?
TIA
Noogle
Edited by - original_noogle on 10/6/2003 7:59:13 AM
Writng Server Scripts
Greetings,
I just had an interview, and bombed it, becasue I had no experience with writing VB/VBA scripts for the server to configure client machines.
Can someone direct me to some example code or tutorails regarding network administation with vb/vba scripts? I'd really appreciate it.
Maybe someone experienced in network scripting can give me a heads up and point me to some resources that might be helpful?
Thank you,
Jim
RegSetValueExString Not Writng Key Value Using Visual Basic 6
Hi every one,
I have being facing this problem for the past one week and can not find a solution to it.
The problem is that my aim was to write key values in registry using visual basic 6 using win 32 api's function "RegSetValueExString".
The problem is that the function executes but no effect is seen in the registry for the respected value.
The the long value returened by this functioin is "5".
Please send me some hints regarding this problem.
Its really urgent.
I'll be grateful to you.
How To Import Excel Data From A Closed Excel File To Access Table?
Hi friends,
i have the following Excel sheet
A B
1 BILL PAID
2 CM20 650.00
3 CM32 1750.12
I want to import the same data into an Access table with the same field name.
Any help?
Thankx and regards
Haris
_____________________________________________________________________
Edited by - harisraz on 6/18/2006 1:33:00 AM
Excel VBA Code For VB6 To Copy Data From Web
this is the excel vba code to copy data from web page to excel sheet How can I use it in vb6 to store data either in a database or excel file or text file so that saved data can be retrived later ?
ExcelVBA Code:
Code:
Sub Macro1()
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://money.rediff.com/money/jsp/co_results_q.jsp?companyCode=15150021", _
Destination:=Range("A1"))
.Name = "co_results_q.jsp?companyCode=15150021_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "16,17"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=-21
End Sub
Can this be done in vb6?
VB Code To Export Excel Data Into MS ACCESS
Hi ! to all my seniors
Can you help me in writing a VB Code
1. to export EXCEL Data into MS ACCESS
2. to Export Txt File Data into MS ACCESS
Waiting eagerly for your reply
Thank in Advance
Regards
madhusrr
Excel Data Check In VB Code..URGENT!!!
Hi All,
Pls. can someone help me read the Excel file (ie. .xls ) thru' VB. How to get the filled
Row count in the excel file thru' VB? How do I iterate thru' the rows? I want to check each row against a table content in my database.Also ,if the row has any error ,a column has to be
appended to mention the error.This is really very Urgent!! I just have opened an Excel file using
the Excel object in VB.
Thanx in advance
Indu
Import Data From Excel File Into New Tab Of Another Excel File
I am new to Visual Basic, so sorry if this is an extremely simple question.
What I want to do is create code that will prompt the user to choose an Excel file to import data from, and have this data copied to a newly created tab in another Excel file.
In other words, the user will already have an Excel file opened, and will click on a button that says something like "Click here to import new data." This will open a Windows Explorer window from which they will choose the file containing the desired information. The values contained in the selected file will be imported into a newly-created tab in the Excel spreadsheet that is already open. Preferably, the tab will have the same name as the file name from which the data is imported (i.e. the tab will be called "Q1 Revenue" if they choose "Q1 Revenue.xls" to import from.)
Thanks for the help.
Help With Simple VB Code To Normalize Excel Worksheet Data
I've wasted the better part of 12 hours trying to work through this problem on my own and I've come to the conclusion my programming skills have long since left me. Can someone please help me through a simple VB macro to iterate through a range of cells and write values to another worksheet?
Basically I'm trying to transform the output from a report I generated in a way that I can run it through a pivot table. Currently its not in good form, the first seven columns are row metadata, but then 184 columns follow representing daily values for each row since the beginning of the year.
Col1 Col2 Col2 Col4 Col5 Col6 1/1/07 1/2/07 1/3/07 1/4/07
AAA AAA AAA AAA AAA AAA 203 450 500 643 888 934
BBB BBB BBB BBB BBB BBB 333 342 456 567 222 091
All I want to be able to do is iterate through the range of colums, copy the value of the current cell, the date represented by the column header, and the corresponding value in Column 1 and write each as a row in blank worksheet.
For example:
AAA 203 1/1/07
BBB 342 1/1/07
I hope I've been clear enough in describing my dataset and what I'm trying to accomplish. If anyone could please show some mercy and guide me through this I'd really appreciate it.
Thank you!
Brian
Query Data From Access By Writing A Code In Excel
Here is what I am doing - create a code in Excel to bring data from Access Database in the same folder. Two problems on the following code: 1) when I used double quote for DatePart , the quoted part (like "yyyy") is highlighted as compile error. 2) it also shows the problem in the line of " .Open, , 3, 3 "
I got this code from a friend and just make minor change and expect it to work propertly. It turned out i met these two problems. Please give me a hint how to fix it. Many thanks.
Code:
Sub Import2()
Dim cn As Object, rs As Object, myCallYear As String, myCallMonth As String
Dim MySql As String, dbfullname As String, myCnt As Long
dbfullname = "C:Documents and SettingsxyzDesktopFFR.mdb"
myCallYear = Sheets("Reference").Range("E13").Value 'Pass Year
myCallMonth = Sheets("Reference").Range("E14").Value 'Pass Month
MySql = "SELECT DatePart(''yyyy'',[TransactionTime]),DatePart('m',[TransactionTime]),[Category],[Subcategory],Sum([DollarSpend]),[TransactionTime]" & _
"FROM tblExpense GROUP BY DatePart('yyyy',[TransactionTime]),DatePart('m',[TransactionTime]),Category,Subcategory" & _
"Having DatePart("yyyy",[TransactionTime]) ='" & myCallYear & "'" & "AND DatePart("m",[TransactionTime]) ='" & myCallMonth & "'Order By DatePart("yyyy",[TransactionTime]), DatePart("m",[TransactionTime]);"
myCallYear = Empty
myCallMonth = Empty
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& dbfullname & ";" 'Create DB connection
Set rs = CreateObject("ADODB.Recordset")
With rs
Set .ActiveConnection = cn
.Source = MySql 'Pass your SQL
.Open , , 3, 3 '.Open , , adOpenStatic, adLockOptimistic
myCnt = .RecordCount
If myCnt > 0 Then
.MoveLast: .MoveFirst
'Pull data to first sheet, cells a1:RecordestCountRow & column 3 _
3 fields in the sql pass
Sheets("Data").Range(Cells(2, 14), Cells(myCnt + 1, 21)).CopyFromRecordset rs
End If
.Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing
End Sub
Uploading Excel Data/records Into Table...what's Wrong In My Code?
Basically, I want to copy all the data which is in my excel sheet to my table. I have succesfully connected into my database using this code:
Set newworkspace = Workspaces(0)
ODBC_DSN_Type = "ODBC;DATABASE=;DSN=BCP"
Set dbase = newworkspace.OpenDatabase("", False, False,
ODBC_DSN_Type)
and then I began uploading my excel data into my table (table is currently empty) using this code:
Dim Rnum As Integer, Cnum As Integer
Dim CelVal As String
''''UPLOAD EXCEL DATA INTO TABLE''''
Set xlapp = CreateObject("Excel.Application")
Set xlbook = xlapp.Workbooks.Open(pathfile & "" & file)
Set xlsheet = xlbook.worksheets(1)
xlsheet.Select
xlapp.DisplayAlerts = False
xlsheet.Rows("1:1").Delete 'field names in excel sheet
'''count the number of rows in the excel file
Do
xlsheet.Cells(Rnum + 1, 1).Select
CelVal = xlsheet.Cells(Rnum + 1, 1).Value
Rnum = Rnum + 1
Loop Until CelVal = ""
Set dumptable1 = dbase.OpenRecordset(tablename,
dbOpenDynaset)
ReDim RecArray(dumptable1.Fields.Count)
Cnum = dumptable1.Fields.Count
For rrow = 0 To Rnum - 2
dumptable1.AddNew
xlsheet.Select
For ccol = 0 To Cnum - 1
xlsheet.Cells(rrow + 1, ccol + 1).Select
RecArray(ccol) = Trim(xlsheet.Cells(rrow + 1, ccol
+ 1).Value)
dumptable1(ccol).Value = RecArray(ccol)
Next ccol
dumptable1.Update
Next rrow
uploading my excel data is almost successful. However, whenever it comes to row 64 of my excel where my records is, it gives me an error message:
“ODBC – call failed. (Error 3146)” which means
Using an ODBC connection, you tried to perform an operation on data in an ODBC data source. This error may occur when the ODBC data source is on a network drive and you aren't connected to the network. Make sure the network is available, and then try the operation again.
I don't believe that the connection is the problem since when i look up to the current records of my table using MS Access after error occurred, i see all 63 records in my table but somehow the above error will pop-up & the copying of records will not be continued. it will stopped to the 63rd row of my excel and i have like 540 racords/rows with 8 fields/columns still to be added.
Can somebody tell me why is this happening? I have observed that when I tried to do in excel a copy & paste of selected rows and save it into the same file that i use for uploading. this problem occurs but i don't know how to fix it.
pls. help me. i really, really need your help. thank you very much.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~
RESOLVED Simple Code For Import Data From Excel To Access
Ciao Gurus,
I need a simple VB code that, after the connection with access, allows to update an access table starting from an excel table.
In access, I created a "TransferSpreadsheet" macro where I imported data from excel file and I believe to see the SQL code but it's not possible.
Thanks
Edited by - kaiala on 1/9/2007 11:11:36 PM
Visual Basic Code For Exporting Excel Data To Access Database
hello all,
There is this data in excel sheet that has to be taken into access database for further processing,
so all the columns from excel need not be exported to access.
There will be only one sheet in Excel.
The number of columns in Excel are fixed but number of rows keep changing .
Another problem is that the format of entry in excel file is to insert a blank row after entering a valid entry, so it is also required to check the blank rows.
Could you please post me a logic which I could use to solve this problem, and could you please post me the code to export data from excel to access.
Thanks & Regards
Shibu P
This message has been scanned by McAfee Virus Protection System
Save An Excel File As A .csv In Code
I am trying to save an excel file as a .csv through VB code. This file is being used to impot into a database. When I save the file manually as a .csv, the import runs fine, but when I save it as a .csv through VB code, the import doesn't run.
Any suggestions would be helpful.
Thanks
Excel Opening .txt File Code?
Hi,
Can someone please help me with some simple vb code for my excel macro. I wish to open a .txt file which will have a specific name based on what is contained in a certain cell in my spreadsheet. Can this be done and if so what coding would I use? I can get the a .txt file to open based on renaming the file to a certain name but I wish to be able to tell it what the name is by typing text into a cell.
Thanks
Import Text File In Excel Using VB Code
Hi folks,
My problem is that I am trying to automate a system in which a text file is brought into a worksheet. The code I have so far stops at the "Import Text File" window looking for the "Import" or "Cancel" button to be pressed. Even if I click the "Import" button, then the Import Text Wizard comes up and I have to click "Finish". Does anybody know the commands to avoid having to press these buttons and have the file come into the worksheet without user intervention.
The file name is a variable called FileValue. Here is the code:
SendKeys "^{Home}", True
ChDir "O:BakeryOrdRecapTextFiles"
With Selection.QueryTable
.Connection = "TEXT;O:BakeryOrdRecapTextFiles" & FileValue
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 9, 9, 9, 1, 9, 1)
.TextFileFixedColumnWidths = Array(11, 10, 32, 5, 8, 28, 15, 13)
.Refresh BackgroundQuery:=True
End With
Code For Opening An Excel 2000 File
I need to open and read an excel 2000 file from my application. I use the following codes currently:
Set dbExcel= OpenDatabase(ExcelFilePath, False, False, "Excel 5.0;HDR=YES")
Set rsExcelFile= dbExcel.OpenRecordset(SheetName)
This does not work for Excel 2000 and beyond.
How do I code for these? Is this possible from VB5?
Advanced: Excel VB File Code Help Needed
Hello I am needing some information/code to do the following:
I am using the infamous macro Sub Auto_Open() to do this. For this example we will use a .txt filename called 'users.txt' which is always located in 'C:store'. Keep the following in mind: The 'C:store' directory is located on 20 different computers. The C: drive is a local drive and the G drive is on the network. I have a user base of 20 people. Each person has their own directory located in 'G:store'. Let's use 'John Doe' for this example which their directory would be 'G:storeJohn Doe. When John Doe loads his .XLS file the code will move the file from 'C:store' to his directory 'G:storeJohn Doe'. Remember I said 20 users. Each user loads the same Excel file from their own directory located in 'G:store' on each of their own computers. When the .xls file is opened from each user located in their own directory on the network I want to be able to have the file 'C:storeusers.txt' to be copied to location 'G:storeUser Directory' and then have the original file in 'C:store' deleted completely. It sounds like I can use the MOVEFILE option but I am having a difficult time getting it to work. Remember say: 20 users open their .xls files in their respective directories. The code will copy/move the 'C:storeusers.txt' file from each of their computers to their own directory on the network 'G:store' and the original 'C:storeusers.txt' file gets deleted. The Excel file opened up by each user will always be located in their own directory. Please help me out. You guys have made it happen with the button move to unused rows for me not too long ago and I know you can help me out again. Any other questions please ask me and I hope what I wrote is not confusing. Thanks!
Problem In Update Excel File From VB6 Code
Hello,
I have a problem
I did a simple test of finding specific string in Excel Sheet,
When I find it I need to update few cells next to the string.
The problem is that I get all kind of excel Files from different customers and sometimes I have a value in the cell, but when I run on the RS from the VB it doesn't see it.. (As it a Null value) do you have any Idea what is the problem..
I will attach a small test that I did, that also have the problem..I dont see all of the Cells info ...
Please try to help me....
Eran
eran@idnext.co.il
CODE Sample : I use to test all the data on Filed C
***************************************
Public Function fOpen_ConnDBExcelFrmActive()
'Connect to the Excel DB
'Created by Rinat Sade - Yaaf Sys
'
On Error GoTo function_error
Dim objData As New CDBData
Dim pszConnection As String
Dim pszDataBase As String
Set objCn = New ADODB.Connection
If objCn.State = adStateOpen Then
If objRs.status = 1 Then
objRs.Close
Set objRs = Nothing
End If
objCn.Close
End If
Dim glTheError As Long
'get string connection
pszDataBase = frmActiveForm.txtExcelFile.Text
'glTheError = objData.fGetDBConnection(11, pszDataBase, "")
pszConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & pszDataBase & _
"; Extended Properties=""Excel 8.0;HDR=No;"""
'Open connection
objCn.Open pszConnection
Set objData = Nothing
Dim pszString As String
Set objRs = New ADODB.Recordset
If objRs.State = adStateOpen Then
objRs.Close
End If
objRs.CursorType = adOpenStatic
' Use client cursor to allow use of
' AbsolutePosition property.
objRs.CursorLocation = adUseClient
' Select Case lDataBaseType
' Open the recordset
' Case Is = enExcel_2000, Is = enExcel_97, Is = enExcel_5, Is = enExcel_4, Is = enExcel_3
pszString = "[" & frmActiveForm.txtTable.Text & "]"
' Case Else
' pszString = pszTable
' End Select
objRs.Open pszString, objCn, adOpenStatic, adLockOptimistic
Do While Not objRs.EOF
If Len(objRs.Fields("f3").Value & "") <> 0 Then
Debug.Print objRs.Fields("f3").Value
End If
objRs.MoveNext
Loop
Exit Function
function_error:
Debug.Print err.Description, err.Number
End Function
Import Text File Into Excel Via VB6 Code
I have a problem that is driving me nuts.
I wish to put a text file into the sheet 2 of an Excel spreadsheet as I have info in sheet 1 which will use sheet2 data. How can this be done. All my code is written in VB6 and do not want to use Excel macros. All I want is to import a text file, surely it is not that difficult I think.
Help
Opening Excel File Using VBA Word Code
Hi all,
I am new to VBA.
I need to open an Excel document when i click menu item which i have created for my
project.
I am working on Word VBA, so i need to write the code in Word VBA code window.
Please reply me aeap.
With Regards,
Suresh.
Saving A Copy Of An Excel File Without Code
I want to save a copy of a workbook to another file location on closing the file. The original is write restricted and the copy saved to a read only location for public viewing. I used the code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"C:DataTest.xls"
Application.DisplayAlerts = True
End Sub
This works well until the workbook is opened and closed from this location. When closing the workbook I get a 1004 Error, trying to save to it's own closed self?? In fact in the read only copy I don't even need the VB code which saves the copy.
How can I get around this?
rgds
denis
Problems In Saving An Excel File Using Vb Code
hi!!
i want to save a *.xls file to *.csv file thro my vb application
here's the vb code i am using
Dim wkb As Workbook
Private Sub Form_Load()
Set wkb = GetObject("c:My Documentsook1.xls")
wkb.SaveAs "book1.csv"
End Sub
but this dosen't seem to workout.it is creating a csv file but is not adding the contents of the xls file or is creating a csv file with improper format.i.e whenever i double click on the csv file excel is opening but the file is not opening
i have added excel9.0 lib from the refrences..
plzz chk out if this code is working on u'r machine's or help me out
Hi! How Do I Code The Filepath For A Data File?
Hi! I'm working on a school project and I have a random access file. In the code I need to refer to the file but I want other people to access it from where ever they stick my program.
How do I make the path to the file the same as the programs path?
i.e. -- ...open "[filepath that the program is in]data.dat" for ...
If you could help me with my newbie question I would be greatfull!
Code Downloading File But Not Data
I am using the following code to download a csv file internet www.nseindia.com however the code is downloading the file but not the data Wherer it is going wrong?
Code:
Private Sub Command1_Click()
Dim Size As Long, Remaining As Long, FFile As Integer, Chunk() As Byte
Inet1.Execute "http://www.nseindia.com/content/equities/scripvol/datafiles/11199429112007BINANICEMEQN.csv", "GET"
Do While Inet1.StillExecuting
DoEvents
Loop
Size = CLng(Inet1.GetHeader("Content-Length"))
Remaining = Size
FFile = FreeFile
Open "c:11199429112007BINANICEMEQN.csv" For Binary Access Write As #FFile
Do Until Remaining = 0
If Remaining > 1024 Then
Chunk = Inet1.GetChunk(1024, icByteArray)
Remaining = Remaining - 1024
Else
Chunk = Inet1.GetChunk(Remaining, icByteArray)
Remaining = 0
End If
Put #FFile, , Chunk
Loop
Close #FFile
MsgBox "File downloaded"
End Sub
Header/Footer In Excel File... Slow Code
Hi,
I have been using the following VBA Code as a macro in my excel files which removes all header/footers from the excel files. However, it seems to be very slow.
[vbcode]
Application.Calculation = xlCalculationManual
Application.StatusBar = "Removing Headers/Footers from " & MyFile & " ....(Please Wait)"
WS_Count = ActiveWorkbook.Worksheets.Count
For j = 1 To WS_Count
With ActiveWorkbook.Worksheets(j).PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
Next
Application.Calculation = xlCalculationAutomatic
[/vbcode]
Please can someone help me with increasing the speed.
Or can someone help me in adding a code which shows a progress bar so that the user does not get the impression that the system has crashed.
Thanks,
thevbgirl
Optimizing Code To Print A 2 Page Excel File
I have built a Timesheet program from scratch (VB6/Access DB). In it, we have the option of putting information on a separate page to show descriptions of what we did during the week (everything, not billable, or billable).
If there are more than 36 entries for the week, the program creates a new page for the rest of the items, but puts the description on the 2nd page. I would like to change this so that the entries that are on the page would have the corresponding descriptions on the back, but I'm not sure how.
Here's the relevant code:
Code:
'Print 2 page timesheet
Public Sub PrintTimeBack()
Dim TFileName As String, CurJob As String, CurTask As String, BottomRow As Long
Dim Row As Long, Found As Boolean, TheDate As Date
Dim Records As Integer, SetOnce As Boolean, BottomRecords As Integer
Dim Index As Integer, JobId As Integer, Selection As String, TempRow As Long
Dim ResetSheet As Boolean, ResetOnce As Boolean, TBackNo As Integer
CallingForm = "PrintTimeBack"
On Error GoTo ErrHandler
SetTempPrint
ReDim JobPrinting(0)
Jobs = 0
'Load up hours table so it is sorted by Job# asc, Taskcode asc
ChangeHoursPrintable
'Set up control information
Records = 0
BottomRecords = 0
ResetSheet = False
ResetOnce = False
SetOnce = False
'Move to first item in table
If Not rstHours.BOF Then
rstHours.MoveFirst
End If
'Show the user some stuff
fraSavPrint.Visible = True
lblInfo = "Setting up 2 Page..."
pbSave.Value = 0
'Set file name
TFileName = "timesheets s_" & Replace(Right$(CurWeek, _
(Len(CurWeek) - 4)), "/", "_") & "_2pg"
'Set up time into array for later printing, this will get all the
' job numbers and codes summed together
Do While Not rstHours.EOF
Index = 0
Found = False
'See if find item in array
Do While Not Found And Index < Jobs
If LCase$(JobPrinting(Index).Num) = _
LCase$(rstHours.Fields("jobno")) And _
LCase$(JobPrinting(Index).Code) = _
LCase$(rstHours.Fields("jobcode")) Then
JobId = Index
Found = True
End If
Index = Index + 1
Loop
'If you don't find it, create new array item
If Not Found Then
If Jobs > 0 Then
ReDim Preserve JobPrinting(Jobs)
End If
Jobs = Jobs + 1
CurJob = rstHours.Fields("jobno")
CurTask = rstHours.Fields("jobcode")
SetJobPrinting (Jobs - 1), CurJob, CurTask
JobId = Jobs - 1
End If
'Switch on what weekday the jobdate is
' Then put the task code and hours into the array
Select Case Weekday(rstHours.Fields("jobdate"))
Case vbMonday
JobPrinting(JobId).Mon = JobPrinting(JobId).Mon + _
rstHours.Fields("hours")
Case vbTuesday
JobPrinting(JobId).Tue = JobPrinting(JobId).Tue + _
rstHours.Fields("hours")
Case vbWednesday
JobPrinting(JobId).Wed = JobPrinting(JobId).Wed + _
rstHours.Fields("hours")
Case vbThursday
JobPrinting(JobId).Thu = JobPrinting(JobId).Thu + _
rstHours.Fields("hours")
Case vbFriday
JobPrinting(JobId).Fri = JobPrinting(JobId).Fri + _
rstHours.Fields("hours")
Case vbSaturday
JobPrinting(JobId).Sat = JobPrinting(JobId).Sat + _
rstHours.Fields("hours")
Case vbSunday
JobPrinting(JobId).Sun = JobPrinting(JobId).Sun + _
rstHours.Fields("hours")
End Select
rstHours.MoveNext
'Show the user that the program is still running by increasing
' the progress bar
If (pbSave.Value + (rstHours.RecordCount / Jobs)) > 100 Then
pbSave.Value = 100
Else
pbSave.Value = pbSave.Value + (rstHours.RecordCount / Jobs)
End If
Loop
'Load up template file
SetExcelFile App.Path & "" & "templatelankwback.xls"
'Use the timesheet front for the sheet
Set Excel_Sheet = xlBook.sheets("Timesheet Front")
'Set up information at the top of the page
SetExcelTop TheDate
'Set up row information
Index = 0
Row = 3 'Top row to put information in
BottomRow = 39 'Bottom row to put information in
SetOnce = False
'Put data from array into sheet
Do While Index < Jobs
'If all rows are filled (CheckReset checks this), reset sheet check
If Not ResetOnce Then
If Not ResetSheet And CheckReset Then
ResetSheet = True
End If
'Actually reset the sheet
If ResetSheet Then
EndExcelSheet TFileName
SetExcelFile App.Path & " emplatelankwback.xls"
Set Excel_Sheet = Nothing
Set Excel_Sheet = xlBook.sheets("Timesheet Front")
SetExcelTop TheDate
Row = 3
BottomRow = 39
SetOnce = False
SetTempPrint
If Right$(TFileName, 4) = ".xls" Then
TFileName = Left$(TFileName, Len(TFileName) - 4) & "_2" & _
Right$(TFileName, 4)
Else
TFileName = TFileName & "_2"
End If
ResetOnce = True
End If
End If
'like "##9##" goes at bottom
'not like "##9##" goes at top of sheet.
If JobPrinting(Index).Num Like "##9##*" Then
'start at bottom of the sheet
TempRow = BottomRow
SetOnce = True
Else
'start at top of the sheet
TempRow = Row
SetOnce = False
End If
'Find next open item in the database
Do While PrintedRow(TempRow) = True
If SetOnce Then
TempRow = TempRow - 1
Else
TempRow = TempRow + 1
End If
Loop
'Put information into sheet
PrintRow Index, TempRow
'There is information in this row now
PrintedRow(TempRow) = True
Index = Index + 1
Loop
'Print sheet into actual excel file
PrintToExcel
'Set current printing date and time
SetCell 42, 2, Format(Now, "Short Date")
SetCell 42, 6, Time
ChangeHoursWeekly
If Not rstHours.BOF Then
rstHours.MoveFirst
End If
Row = 3
Set Excel_Sheet2 = xlBook.sheets("Timesheet Back")
SetCell2 1, 2, MyPrintName
'Job number selects what goes on the back sheet
' option 1 would have everything
' option 2 would have a 9 in the 3rd position (non-billable time
' option 3 would have anything else in the 3rd position
If lstSheets.Selected(1) Then
Selection = "#####*"
ElseIf lstSheets.Selected(2) Then
Selection = "##9##*"
Else
Selection = "##[012345678]##*"
End If
Do While Not rstHours.EOF
'Back of sheet gets selected time time
' If the job number matches above selection, put it on the sheet,
' otherwise skip it
If rstHours.Fields("jobno") Like Selection Then
Found = False
'Find matching name from listing in another table
If Not rstJobNos.BOF Then
rstJobNos.MoveFirst
End If
Do While Not rstJobNos.EOF And Not Found
If rstHours.Fields("jobno") = rstJobNos.Fields("jobnumber") Then
SetCell2 Row, 1, rstHours.Fields("jobno") & _
" / " & rstJobNos.Fields("jobcompany")
Found = True
Else
rstJobNos.MoveNext
End If
Loop
'Put the Job number
SetCell2 Row, 3, Format(rstHours.Fields("jobdate"), _
"Short Date")
'If there is a task code, display it.
' example of task coded entry: 1 - Misc (0.25 hours)
' example of non coded entry: Misc (0.25 hrs)
' The replacethiswiththat function clears the newline character
If Not rstHours.Fields("jobcode") = "" Then
SetCell2 Row, 4, rstHours.Fields("jobcode") & _
" - " & ReplaceThisWithThat(rstHours.Fields("description"), _
Chr(13), " ") & " (" & Format(rstHours.Fields("hours"), _
"#0.00") & " hrs)"
Else
SetCell2 Row, 4, _
ReplaceThisWithThat(rstHours.Fields("description"), _
Chr(13), " ") & " (" & Format(rstHours.Fields("hours"), _
"#0.00") & " hrs)"
End If
Row = Row + 1
End If
'Again, show user that stuff is happening
If pbSave.Value + (45 / rstHours.RecordCount) > 100 Then
pbSave.Value = 100
Else
pbSave.Value = pbSave.Value + (45 / rstHours.RecordCount)
End If
rstHours.MoveNext
Loop
Code Help: Transfer VB Userform Info To Excel File
Using VBA in excel, I want to tranfer my info from my user form in VB to excel. This is the code I hvae started with, but my info won't transfer. Any suggestions?
'Click Justify
Private Sub justifybutton_Click()
End Sub
'Transfers data from UserForm1 to Feeders sheet
Sub transferData1()
Application.Workbooks(1).Worksheets(6).Range("c14").Value = frmUserForm1.customer.Value
Application.Workbooks(1).Worksheets(6).Range("c13").Value = frmUserForm1.hub.Value
Application.Workbooks(1).Worksheets(6).Range("c15").Value = frmUserForm1.distance.Value
Application.Workbooks(1).Worksheets(6).Range("c18").Value = frmUserForm1.stops.Value
Application.Workbooks(1).Worksheets(6).Range("e18").Value = frmUserForm1.numpkgs.Value
Application.Workbooks(1).Worksheets(6).Range("e19").Value = frmUserForm1.wtppkg.Value
Application.Workbooks(1).Worksheets(6).Range("e20").Value = frmUserForm1.volppkg.Value
Application.Workbooks(1).Worksheets(6).Range("g18").Value = frmUserForm1.pallets.Value
Application.Workbooks(1).Worksheets(6).Range("g19").Value = frmUserForm1.wtppallet.Value
End Sub
Code To Extract Data From Text File
can someone supply me with some code to extract data from a text file from its header to the next blank line which would be the end of the section
plz
Add Data To Excel File
Hi
I know how to add the data to a new excel file.
But, how can we append some text to an already existing xls file. I Just want to add some data to an existing file say C:Sample.xls, at C12 location?
Thanks,
How Can I Take All The Data From An Excel File?
Hi,
I have managed to take the data from an excel file but only from one sheet at a time.I used the following code:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CommonDialog1.FileName & ";Extended Properties=Excel 8.0;"
rs.Open "select * from [Sheet1$]", conn, adOpenStatic
That code is working perfect only if i have a sheet with a specific name and only if I want to get the data from that sheet.
What I want to do is to get all the data from the excel file, no matter how many are the sheets or their names.
Can anyone help me? I'm really desperate
Thank you,
Thodoris
Getting Data From An EXCEL File
REally need help on this one...
How do I get a column of data from Excel and output these as items in a ComboBox? Bear with me on this, coz I've only been around VB for a month...
Help would really be appreciated. Enlighten me! Thanks!
Getting Data From An Ini-file With Vba In MS Excel
I need to know how to get saved data in an ini-file into a form in MS Excel by vba. Word is no problem, but I don't get it working in MS Excel. Just now I'm trying to get into a messagebox first, but the box is empty when I'm running the code.
code
msgbox getsetting("c:UserInfo.ini", "UserInfo", "NameFirst"), vbInformation
I have an Excel-file from an another person which is using this file. I can't reach their code because it's locked with password.
ini-file - UserInfo.ini
[UserInfo]
NameFirst=aa
NameLast=bb
PersonNr=cc
Division=dd
Servicelocation=ee
Company=Thiscompany
Logo=Thiscompany.jpg
Excuse my school english.
Regards
Annikatbg
Powerpoint Code To Close Excel File Working Incorrectly
Hello,
I am trying to automate a process that runs an access query, transfers the spreadsheet to an excel page, populates charts and graphs, and then uses these charts and graphs with other formatting to create PowerPoint slides.
Although my code seems to work well, when I log off the computer I am prompted to save changes for an "open" excel file, although it doesn't appear to be open. I think this is creating problems with my transferspreadhseet function in Access, because I am getting the error message "Too Many Fields Defined".
Here is a sample of my ppt code:
Set xlApp = CreateObject("Excel.Application")
Set xlWrkBook = xlApp.Workbooks.Open("\Sfa19pmd$ReportingPatient Reporting - TSIPatient Financial ReportingTemplate DatabasesCharts Master template.xls")
' Create a new slide
ActiveWindow.View.GotoSlide Index:=ActivePresentation.Slides.Add(Index:=1, Layout:=ppLayoutTitleOnly).SlideIndex
' Copy picture of the 1st chart object onto the clipboard
xlWrkBook.Worksheets("IP Variance Chart").ChartObjects("IP Variance Chart").Chart.CopyPicture
and so on.... until I reach the end of my code:
' Close the open workbook without saving changes
xlWrkBook.Close (False)
xlApp.Quit
Set xlApp = Nothing
Set xlWrkBook = Nothing
I a wondering if I am opening the Excel file too many times and am missing code that would work better??
Your help is so much appreciated!!
Thanks
VB Code To Save Excel Spread Sheet To Acrobat File
Hello,
I have multiple excel files which included many tabs in there. I want to save each tab to each Acrobat file. I had a hard time to save the file in .pdf with the name I provided. Is anyone can help me to write the code that will convert the excel format to pdf format. Thank you so much in advance.
Here my code that does not work for me.
Excel.ActivePrinter = "Adobe PDF on NE02:"
ActiveWorkbook.PrintOut , , , , "Adobe PDF", True, True, "\Test" & stTab & ".pdf"
Is there any other way to do this better.
Thank you a lot with your help here
I Need The Source Code For Saving Data To A Preference File!
I need the source code for the followign things: Saving data to a preference file, and accessing it later. (Like a save your current settings option- like a .cfg file) I also need to know how to load the smae file for use later. I have just 1 more thing if it isn't too much of a bear - the Source code for visiting a website through the click of a button.Thank you for your time
------------------
Webmaster Czaries
http://www1.50megs.com/css
|
TRACKER
