|
Hello Everyone,
Can you please advice on why I cant connect to excel file without saving it on my machine.
I tried using these to connection string but still got an error External table is not in the expected format.
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=Excel 12.0;"
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties=Excel 8.0;"
When I try to open and save as the spreadsheet I can successfully connect. The file is on FTP and will be downloaded by automation and get information. I cannot make it automatic if I need to open the excel file and save it.
The terminal I will deploy the program dont have office installed so I need to use a different tool to get data. Any suggestion appreciated thanks in advance.
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind.
- Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
|
|
|
|
|
Because you cannot use it over an application protocol connection, like FTP or HTTP. That isn't supported and, frankly, is not a reliable connection.
The file MUST be on a drive letter or UNC path.
If the extension of the Excel file ends in .XLSX, you have to use the ACE engine. If it's .XLS you can use Jet.
|
|
|
|
|
I download the file first from FTP to local files then connect locally.
I tried this
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=Excel 12.0;"
but still got the error.
When I open the file and saved locally then retry to use the above connection string, I can now connect.
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind.
- Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
|
|
|
|
|
I'm confused. You never mentioned anything about any error message in your OP nor this post.
You also posted connection string making it appear as though you can't open a connection to the database.
So, what's the problem and what's the error message?
|
|
|
|
|
Ok so I have a spreadsheet which has a .xlsx extension and downloaded this on a FTP site. As I search in the internet you need to use this connection string for .xlsx files using ADODB.
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=Excel 12.0;" But this gives error on conn.Open, this is the error External table is not in the expected format..
When I manually open the spreadsheet on MS Office Excel then save it, I can successfully connect to said spreadsheet.
Note: On my machine I have office installed but on the machines where my program will be deployed don't so I need to use ADODB to read the contents.
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind.
- Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
|
|
|
|
|
hansoctantan wrote: When I manually open the spreadsheet on MS Office Excel then save it, I can successfully connect to said spreadsheet.
Excel, the application, is very, very forgiving in terms of which data it accepts; but sounds like the original file may already be in an invalid format.
In that case, ADODB will fail to read it, as it is not as forgiving as Excel. On the plusside, it is also not as big as the entire Excel-package.
Run your file through a validator, or unzip it and take a look at the contents.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Really appreciate your help but the excel file came from our client so I cannot put it in any site as a sample.
What do you mean by run it on a validator?
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind.
- Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
|
|
|
|
|
To see if it is a valid zipped XML file. Excel even accepts CSV files with the wrong extension.
hansoctantan wrote: What do you mean by run it on a validator?
Something like this[^]. If the file is not valid then the connection will not succeed, but it might still be "good enough" for Excel.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
An Excel .XLSX file is really a .ZIP file with a bunch of XML files in it. Open it in WinZip or 7ZIP and take a look around.
If it isn't a .ZIP file, it's not really a .XLSX file and cannot be opened by the ACE engine as your connection string has specified.
|
|
|
|
|
Wow didn't know this. Yeah I can open it on 7Zip.
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind.
- Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
|
|
|
|
|
So I'm on the other end now. I seem to have a prefect string complete with line feeds, formatted to look great as HTML.
I have multiline set to true.
Scenario 1:
I write to RichTextBox.text and I get 1 line
Scenario 2:
I write to RichTextBox.Lines, in which I have 78 lines and I get 2 lines, Line1 with text and Line 2 empty.
Somethings bombing the control to load lines, but I can't figure out what it is.
This is my last try, I tried about 10 things
Private Sub Load_RTB(ByVal p As String)
Dim newLines() As String = p.Split(vbCrLf)
RichTextBox1.Multiline = True
For Each line As String In newLines
line = line.Replace(vbCrLf, "")
Next
RichTextBox1.Lines = newLines
End Sub
From the clipboad, I had to do this
Private Sub PasteAction(ByVal sender As Object, ByVal e As EventArgs)
If Clipboard.ContainsText(TextDataFormat.Rtf) Then
RichTextBox1.SelectedRtf = Clipboard.GetData(DataFormats.Rtf).ToString
ElseIf Clipboard.ContainsText(TextDataFormat.Html) Then
RichTextBox1.SelectedText = Clipboard.GetText(System.Windows.Forms.TextDataFormat.Html)
ElseIf Clipboard.ContainsText(TextDataFormat.Text) Then
RichTextBox1.SelectedText = Clipboard.GetData(DataFormats.Text.Replace("\n", "\r\n")).ToString
End If
End Sub
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
jkirkerx wrote: Dim newLines() As String = p.Split(vbCrLf)
This line cuts up your text into lines. There will not be a CRLF in those lines. If you want to make a "text" out of your lines, you'd join them with a CRLF.
Dim test As String = "1,2,3,4"
Dim items As String() = test.Split(",")
Dim result As String = String.Join(Environment.NewLine, items)
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I'm starting to think it's the Tab char that's preventing the next line to appear.
I have 78 lines, your code example gives me the full first line now. But the next line has a Tab at the beginning.
I'll do some research on this.
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
I ditched my code and threw it in the trash.
I used the RichTextBox controls Load and Save instead, and now it works fine.
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
I want to preserve the linefeeds from the RichTextBox to the file I'm writing to the disk drive.
I can see the line feeds in Debug before I write the file, but after writing, there gone.
I suspect it's the method I wrote to write the file.
Using fileStream As New FileStream(m_path, FileMode.CreateNew, FileAccess.Write, FileShare.ReadWrite)
Dim htmlLen As Long = htmlText.Length
Dim fileData As Byte() = Encoding.UTF8.GetBytes(htmlText)
fileStream.Write(fileData, 0, htmlLen)
End Using
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
That took some thinking for me, first time I've done this. Changed over to StreamWriter for this
Using streamWriter As New StreamWriter(m_path, False, Encoding.UTF8)
Dim charArray As Char() = htmlText.ToCharArray()
Dim charLine As Char() = New Char() {}
For cdx As Integer = 0 To charArray.Length - 1
If Not (charArray(cdx) = Chr(10) Or charArray(cdx) = Chr(13)) Then
Array.Resize(charLine, charLine.Length + 1)
charLine(charLine.Length - 1) = charArray(cdx)
Else
Dim newLine As Char() = charLine
streamWriter.WriteLine(newLine)
Array.Resize(charLine, 0)
cdx += 1
End If
Next
streamWriter.Close()
End Using
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
This is a side cash project, that FoxPro stuff I've been working on and the last bit of code before I ship it today.
I made a dialog box with a Web browser and richtextbox that you can paste HTML into, so it can be injected into an email.
Made the contextMenu with cut, copy and paste, and the event handlers ...
So I can paste from Visual Studio, Word, but not browsers. In the clip board data, I get the StartHTML and EndHTML stuff.
I see some examples in c# for it, custom classes that parse the clipboard, but not anything in VB.
Just wondering if anyone has some code for this, or something down and dirty. It's just going to be used twice a year.
This is the first time I've ever asked for code, just figured why reinvent the wheel on this.
Private Sub PasteAction(ByVal sender As Object, ByVal e As EventArgs)
If Clipboard.ContainsText(TextDataFormat.Rtf) Then
RichTextBox1.SelectedRtf = Clipboard.GetData(DataFormats.Rtf).ToString
ElseIf Clipboard.ContainsText(TextDataFormat.Html) Then
RichTextBox1.SelectedText = Clipboard.GetText(System.Windows.Forms.TextDataFormat.Html)
ElseIf Clipboard.ContainsText(TextDataFormat.Text) Then
RichTextBox1.SelectedText = Clipboard.GetData(DataFormats.Text).ToString
End If
End Sub
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
Hi, can anyone provide me with a copy(Original) of Visual Basic 6.0? If anyone has an extra product that hasn't been used and a valid product key, I would like to purchase, if possible. I DO NOT want any pirated software or do I wish to suggest this. I can't find VB6.0 to purchase...
Thanks in advance.
|
|
|
|
|
Why??
Wikipedia quote: Mainstream Support for Microsoft Visual Basic 6.0 ended on March 31, 2005. Extended support ended in March 2008.
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
Sorry for the late reply, but I have a client that did not want to spend the extra $$$ to pay me to "re-vamp" his project that he paid someone else to write and he wanted some upgrades that the past programmer left him the source code and my newer .net environment would not convert the old stuff. (Without rewriting or "putting a band aide" on his existing source. So, I went with the old VS6 and it worked out for the both of us, even though he still spent just as much...oh well, I tried to reason with him. Thanks for asking why...lol
|
|
|
|
|
|
I found a new VS6 package for $20, thanks! It all worked out... 
|
|
|
|
|
Hello everyone, we are currently doing a school prototype. we made a notification device and we want to ensure that it is returned properly. There is an arduino inside the device so it is possible to connect it to a pc via usb port. The problem is that we don't know how visual basic will know what serial port is used when the device is connected. We want to use the information on what serial port is used as an input data to operate our magnetic locks. Please help us. Thanks in advance
|
|
|
|
|
|
If you have an USB-Device which creates a virtual COM-Port you could get this Information out of the Registry of your PC.
For example you could search for the Vendor-ID and look for the Sub-Information. Here you will find the actual used COM-Port (if this Device is connected) and perhaps other useful Information.
If you are interested I could look for a sample-code for this.
But you should know, that the Registry-Entries could change by different Operating-Systems.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
