|
I have some code that moves data from an Access database into an Excel spreadsheet. I know that there are easier ways of doing this, but for my particular needs I have to have it moved this way.
The code goes through a query in Access and moves data into the spreadsheet with a loop. That part works just fine.
The problem is that I have to format the spreadsheet with this code. All of the different categories of data will be formatted differently. The piece I am looking at now is called "TheTO."
The code I have moves the data and then goes back and formats cells based on their content. It looks for the words in the cells to do this. The problem is that I will have to repeat this code for about 40 different phrases. That seems excessive to me. Also, if any of those phrases change at all, the code won't work.
I want to have the formatting happen during the loop. However, I don't know how to do that.
Here is the code. I am including one block of the code that is formatting the cells. There would be many, many more because the C.Value will be different every time.
TheTO = rs!to
oSheet.Cells(2, 1).Value = TheTO
TheSTOname = rs!STO
oSheet.Cells(3, 1).Value = TheSTOname
TheStaffName = rs!TeamName
oSheet.Cells(3, 2).Value = TheStaffName
theActDesc = rs!ActDesc
oSheet.Cells(3, 3).Value = theActDesc
rs.MoveNext
Do Until rs.EOF
If rs!to = TheTO Then
If rs!STO = TheSTOname Then
If TheStaffName = rs!TeamName Then
If theActDesc = rs!ActDesc Then
rs.MoveNext
Else
theRow = theRow + 1
oSheet.Cells(theRow, 3).Value = rs!ActDesc
rs.MoveNext
End If
Else
'theRow = theRow + 1
oSheet.Cells(theRow, 2).Value = rs!TeamName
TheStaffName = rs!TeamName
rs.MoveNext
End If
Else
theRow = theRow + 1
oSheet.Cells(theRow, 1).Value = rs!STO
TheSTOname = rs!STO
TheStaffName = ""
rs.MoveNext
End If
Else
theRow = theRow + 1
oSheet.Cells(theRow, 1).Value = rs!to
TheTO = rs!to
rs.MoveNext
End If
skip:
Loop
iNumCols = IIf(w > 0, w, iNumCols)
'Format the header row as bold
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
'Size the columns
.Columns("A:A").ColumnWidth = 40
.Columns("B:B").ColumnWidth = 22
.Columns("C:C").ColumnWidth = 73.44
End With
oSheet.Range("A:A").NumberFormat = "dd-mmm-yy "
With oSheet.Range("A1:" & Chr(iNumCols + 64) & rs.RecordCount + 1)
For Each C In oSheet.Range("A1:" & Chr(iNumCols + 64) & rs.RecordCount + 1).Cells
If C.Value = "First TO" Then
With C
.Font.Name = "Arial"
.Font.Bold = True
.Font.Size = 12
.Interior.ColorIndex = 15
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeTop).ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeRight).ColorIndex = xlAutomatic
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideVertical).ColorIndex = xlAutomatic
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
End With
End If
Next C
Thank you, in advance, for any help you can give me!!!!! (I am very new to coding!!)
|
|
|
|
|
You don't need to loop through the Access data to copy it to Excel line by line - Excel has a CopyFromRecordset method[^] which can be used to transfer it in one go.
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
The loop is so that the items are listed correctly and do not repeat like they do in the query in Access. I don't know any way to make it look the way it needs to look without the loop.
Any ideas on the formatting?
|
|
|
|
|
Hi all,
I have been working on a script to do what the subject line says but I'm really not getting anywhere. I feel as though I'm almost there, but I just can't figure out the last part.
My script is as follows:
Option Explicit
Dim strProjectsFolder, strProjectNumber, strCostingsPath, strProjectsLTDGroup
strProjectsFolder = "\\ac1fpcov01.za.if.atcsg.net\Business\Delegation IT\Projects\"
strProjectNumber = InputBox("Please enter the new Project Number", "Project Number")
strCostingsPath = strProjectsFolder & strProjectNumber & "\02. Costing"
strProjectsLTDGroup = "AC1-LS-Finance SSC"
SetPermissions
Function SetPermissions()
Dim intRunError, objShell, objFSO
Set objShell = CreateObject("Wscript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FolderExists(strCostingsPath) Then
WScript.Echo "You're changing the permissions on " & strCostingsPath
intRunError = objShell.Run ("%COMSPEC% /c Echo Y| ICACLS.EXE " & strCostingsPath & " /inheritance:d /remove:g AC1-LS-Finance SSC:(OI)(CI) ", 2, True)
If intRunError <> 0 Then
Wscript.Echo "Error removing inheritance from " & strCostingsPath
End If
Else
WScript.Echo "Error: Project Folder " & strProjectNumber & " does not exist."
End If
End Function
WScript.Quit
I've REMd some lines to simplify while I debug, so I'm currently focusing on the removal of inheritance. I figured if I got that bit right I could move onto the removal of permissions, either in a second IF statement or in the same command line.
But basically, what the script is doing is:
1. Ask the user for the Project number (e.g. P33333)
2. Set variables for the file / folder location based on the project folder on the server
3. Check that the project folder exists, and show a message with the subfolder on which the permissions are being changed.
4. Run the ICACLS command with the /inheritance /T switch to disable inheritance on the project subfolder and all subfolders and files.
5. I've included inRunError to capture any errors, and added another IF statement to show which part of the script is showing the error (only the inheritance section is active at the moment).
6. Finally, an Error trap in case the project folder was entered incorrectly.
Run the script, and all I get is the inRunError that the inheritance command isn't working.
I've taken the exact same command and entered it into a bat file and it works perfectly fine. But in a script it fails every time. I've tried taking all the variables out and working with a skeleton script and still nothing.
So it leads me to believe that I'm either trying to do something with the command that isn't possible, or vbscript just doesn't like me.
Any help would be greatly appreciated.
Thanks
Chris
PS. I should also add, clearly the folder is on a network location. My user account does already have full control permissions over the folder.
It is also an AD security group that I am changing the permissions of in the second part of the script, again it works as-is in a bat file, but not in the script.
|
|
|
|
|
I see you are feeding the stdin of the command-line using the echo command. Two things I would try;
- Try feeding stdin by feeding it from a textfile; one can write stdout to file by routing it (e.g., dir /s >folders.txt), but one can also route stdin. (format <input.txt).
<li>Instead of executing the command with its switches, generate a single batch-file that holds that command as a text, execute it, and delete it.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Dear all,
I am leaner to VB.At present i am trying to get data serially from arduino and plot grapgh time Vs current.I can data serially and print it on text . Now my next process is plotting single line grapgh.
[^]
example show in above link.
I am facing probelm in setting chart permaeter and label them. How to load live data in to chart w.r.t time.
Current working code
Current working code
Imports System
Imports System.IO.Ports
Imports System.ComponentModel
Imports System.Threading
Imports System.Drawing
Public Class Form1
Dim myPort As Array
Dim Distance As Integer
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
myPort = IO.Ports.SerialPort.GetPortNames()
PortComboBox.Items.AddRange(myPort)
BaudComboBox.Items.Add(9600)
BaudComboBox.Items.Add(19200)
BaudComboBox.Items.Add(38400)
BaudComboBox.Items.Add(57600)
BaudComboBox.Items.Add(115200)
ConnectButton.Enabled = True
DisconnectButton.Enabled = False
End Sub
Private Sub ConnectButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ConnectButton.Click
SerialPort1.PortName = PortComboBox.Text
SerialPort1.BaudRate = BaudComboBox.Text
SerialPort1.Open()
Timer1.Start()
ConnectButton.Enabled = False
DisconnectButton.Enabled = True
End Sub
Private Sub DisconnectButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DisconnectButton.Click
SerialPort1.Close()
DisconnectButton.Enabled = False
ConnectButton.Enabled = True
End Sub
Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Try
SerialPort1.Write("c")
System.Threading.Thread.Sleep(250)
Dim k As Double
Dim distance As String = SerialPort1.ReadLine()
k = CDbl(distance)
ListBoxSensor.Text = k
Catch ex As Exception
End Try
End Sub
Private Sub Relay_ON_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Relay_ON.Click
SerialPort1.Write("1")
End Sub
Private Sub Relay_Off_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Relay_Off.Click
SerialPort1.Write("0")
End Sub
End Class
|
|
|
|
|
Quote: I have a project that requires more than 1 Barcode reader in the same pc How to know where the shot had come from the barcode reader., please help me sample coding in vb.net
|
|
|
|
|
This will only be possible if you have an SDK for the sepicific type of barcode reader, and the barcode reader works via that SDK only (i.e. does not emulate a keyboard). You have to read the documentation for the barcode reader.
|
|
|
|
|
|
Some barcode readers are very basic, all they do is fill in whatever control has focus. Meaning, whatever textbox has focus when you scan the barcode it will just dump the string right into the textbox. No logic or complexity to it. So, it will all depend on the barcode reader you have.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
Please I'm using vb.net 2013 and I have a textbox named txtRecipients
Now i want to check for the following:
1. The phone no. should always start with 233 followed by any digit other than zero like: 233201245685
2. Multiple phone numbers should be separated by a comma, like 233201245685, and the comma replaced automatically before the start of another phone number in the same format. So if two phone numbers are entered they should be like this:
233201245685,233547345696
I did something like this in the leave event. Is there any better way to do this as the comma is not getting replaced at a specific position. I used both the remove and replaced functions, but that did not work. Thanks in advance
<pre lang="vb">
Dim str1 As String = Mid(Me.txtRecipients.Text, 1, 1)
Dim str2 As String = Mid(Me.txtRecipients.Text, 2, 1)
Dim str3 As String = Mid(Me.txtRecipients.Text, 3, 1)
Dim str4 As String = Mid(Me.txtRecipients.Text, 4, 1)
Dim str13 As String = Mid(Me.txtRecipients.Text, 13, 1)
If str1.Trim <> 2 Then
Me.txtRecipients.Focus()
MsgBox(The first digit must be 2)
Exit Sub
End If
If str2.Trim <> 3 Then
Me.txtRecipients.Focus()
MsgBox(The second digit must be 3)
Exit Sub
End If
If str3.Trim <> 3 Then
Me.txtRecipients.Focus()
MsgBox(The third digit must be 3)
Exit Sub
End If
If str4 = 0 Then
Me.txtRecipients.Text.Replace(0, String.Empty)
Me.txtRecipients.Focus()
MsgBox(Phone No. must not start with 0. Format e.g. 233243404804)
Exit Sub
End If
If str13 <> "," Then
' Me.txtRecipients.Text.Replace(str13, ",")
Me.txtRecipients.Focus()
MsgBox(Phone No. must not start with 0. Format e.g. 233243404804)
Exit Sub
End If
</pre>
-- modified 23-Sep-14 13:46pm.
|
|
|
|
|
I will use first a split on the comma(Me.txtRecipients.Text.split(",") and walk throu the array. Check the first tree chars in 1 time. string.substring()
check the others with like (string like "233#####")
Jan
|
|
|
|
|
I have this
textbox that users enter their names, but
I realized that they enter their names by entering so many spaces. I used the
replace function but it does not work well. Is there anyway to prevent this at
keypress, when the user tries to press the space bar more than once? Thank you,
all.
|
|
|
|
|
Member 11078565 wrote: I used the
replace function but it does not work well.
What does that mean? Did you use it correctly?
yourString = yourString.Replace(" ", " ")
or with Regular Expressions:
yourString = System.Text.RegularExpressions.Regex.Replace(yourString,"\\s+"," ")
|
|
|
|
|
Trap the keydown/up event, inspect the current and previous characters in the string, if they are both a space then deal with it in the event.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am making a little app and I am stuck. I have a multilined textbox and I would like a button to take the text and reverse the line order. So if the text was numbered each line as 1->9, pressing the button will reorder the lines so they are 9->1.
|
|
|
|
|
Member 11096018 wrote: I am making a little app and I am stuck.
Where are you stuck? Edit your message and show exactly which part you are having problems with.
|
|
|
|
|
i figured it out
Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
Dim counter As Integer = txtNames.Lines.Length - 1
' create array to reverse line order
ReDim fileNames(counter)
For iLine = 0 To counter
fileNames(counter) = txtNames.Lines(iLine)
Next iLine
' using the array building a string starting from the end of the array
Dim list As String = ""
For iLine = 0 To counter
list = list + txtNames.Lines(counter) + vbCrLf
counter = counter - 1
Next iLine
'display results
txtNames.Text = list.Trim
End Sub
modified 20-Sep-14 4:26am.
|
|
|
|
|
Good day friends! Please, does anyone know where I can download ebook for dx 11 and vb?
|
|
|
|
|
Sorry to state the obvious, but I would try Google.
|
|
|
|
|
You're not going to find any books on VB6 any more let alone with DirectX support in them.
Managed DirectX died quite a long time ago but there are third party alternative like SlimDX and SharpDX, though, there aren't any books on those libraries as far as I can find.
|
|
|
|
|
PictureBox1.Left += And() +6
if PictureBox1.left > 1000 then
PictureBox1.location = new point (150, 140)
end if
thanks
|
|
|
|
|
That first line is not valid VB.NET code. It'll never compile so there's really nothing to convert it to.
Seriously, VB6?? It's been dead for quite a long time now.
|
|
|
|
|
Seems that line
PictureBox1.Left += And() +6
is erratic - or do you really have a function named 'And'?? If so you'll have to enclose this VB key word in square brackets like
PictureBox1.Left += [And]() +6
otherwise remove it to:
PictureBox1.Left += 6
This will move your picture box 6 pixel to the right an if resulting position exceeds 1000 pixel, the picturebox position will be reset to a default.
|
|
|
|
|
Hello !
I'm using VB.net 2013 and SQL server 2008R2.
I'm writing a financial application , and I'm not sure about which data type to use.
I know that decimal is more precise that float , but I read that using decimal can significantly decrease the speed of calculations and the speed of storing data to database , so the speed of the application.
Is this true , and if yes is there a solution or should I continue to use float ?
Thank you !
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
