![Excel Vba File Handle Excel Vba File Handle](http://www.modbustools.com/images/excel_mbaxponsheet.png)
![Excel Vba File Handle Excel Vba File Handle](http://www.msc-lims.com/images/diybalancel1.jpg)
Hi! I would like to do the folowing: - Open a HTML file from VBA code ( with a from) - Access tags that have a specific attribute ( namely : class='font' width='220') - copy the value of these table cells into Excel. I am need of handling JSON Object which is the response of XMLHTTPRequest in Excel VBA. I wrote below code but not succeeded. Please guide me. Dim sc As Object Set sc = CreateObject('ScriptCo. Tips about Microsoft Excel VBA programming to ease your business day. Topics on how to use Excel in business process operation. MS Excel VBA training. Free retake policy. Convenient locations. Introduction to Advanced courses.
![Excel Vba File Handle Excel Vba File Handle](http://bsuperiorsystem.com/images/portfolio/Portfolio-KleingGroup-Excel-Commision-Tier.png)
Pro- business Excel VBA Programming. Note: We are not going to discuss here the Consolidation function of Excel. The said function is used if you want to sum up multiple numbers from different Excel files that have same format into a single Excel file. I've got limited time to post to discuss it but will try to tackle it in the near future. In this post, we will combine records from different files with different format. Appending them all in a single Excel file.
I am currently able to enter csv file data into Excel VBA by uploading the data via the code below then handling the table, surely not the best way as I am only interested in some of the data and d.
No summation will be done. Data Consolidation. Is it to much tedious if you copy and paste manually sets of data from multiple files and append them all together in a master file?
Let's say you have 5. And the most boring of all about it is that you need to do it over and over again in a weekly basis. Of course, you will agree with me that it is tedious. If you find yourself in that situation and don't want to do the same thing manually weekly, then you can automate the whole process. The long hours of doing it manually can be reduced in an hour or two with a macro program. Source Data. So let's start by creating the files to be combined together.
![Excel Vba File Handle Excel Vba File Handle](http://4.bp.blogspot.com/-D3v2EE99ai4/UcxZYnM-fdI/AAAAAAAAInc/bwyJjgcWS0Y/w717-h495-no/VBA-figure-2-6.jpg)
For our example, we will create 3 different source files; an Excel file, CSV file, and a Tab delimited file. Excel File - - Create a new Excel file and copy the data in the following screen shot and save as Excel file, of course : )CSV File - - In Excel, copy the data in the following screen shot and save it in CSV format by clicking "Save As" in the File menu. Change the file type to CSV before clicking the Save button.
Tab Delimited - - In Excel, copy the data in the following screen shot and save it in Tab delimited text file by clicking the "Save As" in the File menu. Change the file type to "Tab delimited" before clicking the Save button. Recording Data Import.
AT this point, we will extract records from the files that we created while we are macro recording it. To start the Macro Recorder, please see my post on how to do that at Easiest Way to Learn Excel VBA Programming by Macro Recorder. Once it is up and running, we will do the data extraction of the 3 files. But it is much better if you will start and stop the macro recorder for each file extraction so that each file will have its own procedure module. To extract the file, follow the instruction in my previous post, Export To Excel.
You should generate the same codes as below: Source Code for Excel file. Sub Macro. 1()'' Macro. Macro' Macro recorded 9/1.
Joel. Protusada''With Active. Sheet. Query. Tables.
Add(Connection: =Array( _"OLEDB; Provider=Microsoft. Jet. OLEDB. 4. 0; " & _"Password=""""; User ID=Admin; Data Source=" & _"E: \Consolidation\Sales branch.
Mode=Share Deny Write; ", _"Extended Properties=""HDR=YES; ""; Jet OLEDB: " & _"System database=""""; " & _"Jet OLEDB: Registry Path=""""; Jet OLEDB: " & _"Database Password=""""; Jet OLEDB: ", _"Engine Type=3. Jet OLEDB: Database " & _"Locking Mode=0; Jet OLEDB: " & _"Global Partial Bulk Ops=2; Jet OLEDB: Global " & _"Bulk Transactions=1; Jet OL", _"EDB: New Database Password=""""; Jet OLEDB: " & _"Create System Database=" & _"False; Jet OLEDB: Encrypt Database=False; Jet " & _"OLEDB: Don't Copy Locale", _" on Compact=False; Jet OLEDB: Compact " & _"Without Replica " & _"Repair=False; Jet OLEDB: SFP=False" _), Destination: =Range("A1")). Command. Type = xl. Cmd. Table. Command.
Text = Array("Sheet. Name = "Sales branch.
Field. Names = True. Row. Numbers = False. Fill. Adjacent. Formulas = False.
Preserve. Formatting = True. Refresh. On. File. Open = False. Background. Query = True. Refresh. Style = xl. Insert. Delete. Cells. Save. Password = False.
Save. Data = True. Adjust. Column. Width = True. Refresh. Period = 0. Preserve. Column. Info = True. Source. Data. File = _"E: \Consolidation\Sales branch. Refresh Background.
Query: =False. End With. End Sub. Source code for CSV file. Sub Macro. 2()'' Macro. Macro' Macro recorded 9/1. Joel. Protusada''With Active. Sheet. Query. Tables. Add(Connection: = _"TEXT; E: \Consolidation\Sales branch.
Destination: =Range("A1")). Name = "Sales branch. Field. Names = True. Row. Numbers = False. Fill. Adjacent. Formulas = False.
Preserve. Formatting = True. Refresh. On. File. Open = False. Refresh. Style = xl. Insert.
Delete. Cells. Save. Password = False. Save. Data = True. Adjust. Column. Width = True. Refresh. Period = 0. Text. File. Prompt. On. Refresh = False.
Text. File. Platform = 4. Text. File. Start. Row = 1. Text. File. Parse. Type = xl. Delimited. Text. File.
Text. Qualifier = xl. Text. Qualifier. Double. Quote. Text. File. Consecutive. Delimiter = False.
Text. File. Tab. Delimiter = False. Text. File. Semicolon.
Delimiter = False. Text. File. Comma. Delimiter = True. Text. File. Space.
Delimiter = False. Text. File. Column. Data. Types = Array(1, 1, 1, 1, 1, 1, 1, 1). Text. File. Trailing. Minus. Numbers = True. Refresh Background. Query: =False. End With.
Sheets("Sheet. 3"). Select. End Sub. Source code for the tab delimited file.
Sub Macro. 3()'' Macro. Macro' Macro recorded 9/1. JPCORP''With Active. Sheet. Query. Tables. Add(Connection: = _"TEXT; E: \Consolidation\Sales branch.
Destination: =Range("A1")). Name = "Sales branch. Field. Names = True. Row. Numbers = False.
Fill. Adjacent. Formulas = False. Preserve. Formatting = True.
Refresh. On. File. Open = False. Refresh. Style = xl. Insert.
Text. File. Platform = 4. Text. File. Start. Row = 1. Text. File. Parse. Type = xl. Delimited. Text. File. Text. Qualifier = xl.
Text. Qualifier. Double. Quote. Text. File.
Consecutive. Delimiter = False. Text. File. Tab. Delimiter = True. Text. File. Semicolon. Delimiter = False. Text. File. Comma.
Delimiter = False. Text. File. Space. Delimiter = False. Text. File. Column. Data. Types = Array(1, 1, 1, 1, 1, 1, 1, 1). Text. File. Trailing. Minus. Numbers = True.
Refresh Background. Query: =False. End With.
End Sub. As you can see there are almost no difference between the CSV and Tab file. So in our programming we will modify the codes above and we will create a single subroutine to handle both CSV and Tab files, and a separate subroutine for Excel files. Project Details. Before we start with the actual programming, we need to be precised in the parameters of our consolidation. Below is the brief specifications of it: Modified Macro Program. As I've said we will modify the code above to subroutines that we can use and reuse in our programming.
Below is the modified recorded macro for the Excel file extraction: Sub Extract. Excel. Data(str. Path As String, _str. File As String, str. Sheet As String)Sheets(str. Sheet). Select. With Active. Sheet. Query. Tables.
Add(Connection: =Array( _"OLEDB; Provider=Microsoft. Jet. OLEDB. 4. 0; " & _"Password=""""; User ID=Admin; Data Source=" & _str. Path & str. File & "; Mode=Share Deny Write; ", _"Extended Properties=""HDR=YES; ""; Jet OLEDB" & _": System database=""""; Jet OLEDB: Registry Path=""""; Jet OLEDB" & _": Database Password=""""; Jet OLEDB: ", _"Engine Type=3. Jet OLEDB: Database Locking Mode=0; " & _"Jet OLEDB: Global Partial Bulk Ops=2; " & _"Jet OLEDB: Global Bulk Transactions=1; Jet OL", _"EDB: New Database Password=""""; Jet OLEDB: " & _"Create System Database=False; Jet OLEDB: " & _"Encrypt Database=False; Jet OLEDB: Don't Copy Locale", _" on Compact=False; Jet OLEDB: Compact Without Replica " & _"Repair=False; Jet OLEDB: SFP=False" _), Destination: =Range("A1")). Command. Type = xl.
Cmd. Table. Command. Text = Array("Sheet. Name = "Sales branch. Field. Names = True. Row. Numbers = False. Fill. Adjacent. Formulas = False. Preserve. Formatting = True.
Refresh. On. File. Open = False. Background.
Query = True. Refresh. Style = xl. Insert. Delete. Cells. Save. Password = False.
Save. Data = True. Adjust. Column. Width = True. Refresh. Period = 0. Preserve. Column.
Info = True. Source. Data. File = str. Path & str. File. Refresh Background. Query: =False. End With. End Sub. To test the above procedure, the correct parameters should be supplied like the example below: Sub Test. Extract. Excel. Data()Extract.
Excel. Data "E: \consolidation\", "Sales branch. Parameters are: 'str. Path - folder where to get the file 'str. File - Excel filename of the source data.
Sheet - woeksheet where to put the data. End Sub. Here's the subroutine for the CSV and Tab files extraction: Sub Extract. Text. Data(str. Path As String, _str. File As String, str.
Sheet As String, is. CSV As Boolean)Dim My. Records. Sheets(str.
Sheet). Select. If is. CSV Then. Set My. Records = Active. Sheet. Query. Tables. Add(Connection: = _"TEXT; " & str. Path & str. File, Destination: =Range("A1"))Else.
Set My. Records = Active. Sheet. Query. Tables. Add(Connection: = _"TEXT; " & str. Path & str. File, Destination: =Range("A1"))End If.
With My. Records. Name = Left(str. File, Len(str.
File) - 4)If is. CSV Then. Text. File. Comma.
Delimiter = True. Text. File. Tab. Delimiter = False. Else. Text. File.
Comma. Delimiter = False. Text. File. Tab. Delimiter = True. End If. Field. Names = True. Row. Numbers = False. Fill. Adjacent. Formulas = False.
Text. File. Platform = 4. Text. File. Start.
Row = 1. Text. File. Parse. Type = xl. Delimited. Text. File. Text. Qualifier = xl.
Text. Qualifier. Double. Quote. Text. File. Consecutive. Delimiter = False.
Text. File. Semicolon. Delimiter = False. Text. File. Space.
Delimiter = False. Text. File. Column. Data. Types = Array(1, 1, 1, 1, 1, 1, 1, 1). Text. File. Trailing. Minus. Numbers = True.
Refresh Background. Query: =False. End With. End Sub. Here's the code to test it. Sub Test. Extract. CSVData()Extract. Text. Data "E: \consolidation\", _"Sales branch.
True. End Sub. Sub Test. Extract. Tab. Data()Extract. Text. Data "E: \consolidation\", "Sales branch.
Load csv file into a VBA array rather than Excel Sheet. Okay, looks like you need two things: stream the data from the file, and populate a 2- D array.
I have a 'Join. 2d' and a 'Split. I recall posting them in another reply on Stack.
Overflow a while ago). Do look at the comments in the code, there are things you might need to know about efficient string- handling if you're handling large files. However, it's not a complicated function to use: just paste the code if you're in a hurry. Streaming the file is simple BUT we're making assumptions about the file format: are the lines in the file delimited by Carriage- Return characters or the Carriage- Return- and- Linefeed character pair? I'm assuming 'CR' rather than CRLF, but you need to check that. Another assumption about the format is that numeric data will appear as- is, and string or character data will be encapsulated in quote marks.
This should be true, but often isn't.. And stripping out the quote marks adds a lot of processing - lots of allocating and deallocating strings - which you really don't want to be doing in a large array. I've short- cut the obvious cell- by- cell find- and- replace, but it's still an issue on large files. Anyway: here's the source code: watch out for line- breaks inserted by Stack. Overflows textbox control: Running the code: Note that you'll need a reference to the Microsoft Scripting Runtime (system.
Private Sub test(). Dim arr. X As Variant. X = Array. From. CSVfile("My. File. csv"). Streaming a csv file. Note that I'm assuming your file is in the temp folder. C: \Documents and Settings[$USERNAME]\Local Settings\Temp.
You'll need to use filesystem commands to copy the file into a local folder: it's always quicker than working across the network. Public Function Array. From. CSVfile( _.
Name As String, _. Optional Row. Delimiter As String = vb.
Cr, _. Optional Field. Delimiter = ",", _. Optional Remove. Quotes As Boolean = True _. Load a file created by File. To. Array into a 2- dimensional array.
The file name is specified by str. Name, and it is exected to exist. This is a deliberate restriction.
Remove. Quotes=TRUE strips out the double- quote marks (Char 3. On Error Resume Next.
Dim obj. FSO As Scripting. File. System. Object.
Dim arr. Data As Variant. Dim str. File As String. Dim str. Temp As String. Set obj. FSO = New Scripting. File. System. Object.
Temp = obj. FSO. Get. Special. Folder(Scripting.
Temporary. Folder). Short. Path. str. File = obj. FSO. Build. Path(str. Temp, str. Name). If Not obj. FSO. File. Exists(str. File) Then ' raise an error?
Exit Function. Application. Status. Bar = "Reading the file.. Name & ")". If Not Remove. Quotes Then. arr. Data = Join. 2d(obj. FSO. Open. Text. File(str. File, For. Reading).
Read. All, Row. Delimiter, Field. Delimiter). Application.
Status. Bar = "Reading the file.. Done". ' we have to do some allocation here.. Temp = obj. FSO. Open. Text. File(str. File, For. Reading). Read. All. Application. Status.
Bar = "Reading the file.. Done". Application. Status. Bar = "Parsing the file..".
Temp = Replace$(str. Temp, Chr(3. 4) & Row. Delimiter, Row. Delimiter). Temp = Replace$(str. Temp, Row. Delimiter & Chr(3. Row. Delimiter). str.
Temp = Replace$(str. Temp, Chr(3. 4) & Field.
Delimiter, Field. Delimiter). str. Temp = Replace$(str. Temp, Field. Delimiter & Chr(3. Field. Delimiter). If Right$(str. Temp, Len(str.
Temp)) = Chr(3. 4) Then. Temp = Left$(str. Temp, Len(str. Temp) - 1). If Left$(str. Temp, 1) = Chr(3. Then. str. Temp = Right$(str. Temp, Len(str. Temp) - 1). Application. Status.
Bar = "Parsing the file.. Done". arr. Data = Join. Temp, Row. Delimiter, Field. Delimiter). str. Temp = "". Application. Status.
Bar = False. Set obj. FSO = Nothing. Array. From. CSVfile = arr.
Data. Erase arr. Data. The Split. 2d Function, which creates a 2- dimensional VBA array from a string: Public Function Join. By. Ref str. Input As String, _. Optional Row. Delimiter As String = vb. Cr, _. Optional Field.
Delimiter = vb. Tab, _. Optional Coerce. Lower.
Bound As Long = 0 _. Works like VBA. Strings. Check your lower bounds on return: never assume that any array in.
VBA is zero- based, even if you've set Option Base 0. If in doubt, cource the lower bounds to 0 or 1 by setting Coerce. Lower. Bound. ' Note that the default delimiters are those inserted. ADODB. Recordset. Get. String. ' Coding note: we're not doing any string- handling in VBA.
Strings - . ' allocating, deallocating and (especially!) concatenating strings is SLOW. However, the VBA Join, split, replace functions are linked directly to. VBA standards) functions in the native Windows code. On Error Resume Next. Dim i As Long. Dim j As Long. Dim i_n As Long. Dim j_n As Long. Dim i_l. Bound As Long.
Dim i_u. Bound As Long. Dim j_l. Bound As Long. Dim j_u. Bound As Long. Dim arr. Temp. 1 As Variant.
Dim arr. Temp. 2 As Variant. Temp. 1 = Split(str. Input, Row. Delimiter). Bound = LBound(arr. Temp. 1). i_u. Bound = UBound(arr. Temp. 1). ' clip out empty last row: common artifact in files with a terminating row delimiter. If VBA. Len. B(arr.
Temp. 1(i_u. Bound)) < = 0 Then. Bound = i_u. Bound - 1. Bound. arr. Temp. Split(arr. Temp. 1(i), Field.
Delimiter). j_l. Bound = LBound(arr. Temp. 2). j_u. Bound = UBound(arr. Temp. 2). ' ! potential error: first row with an empty last field.. If VBA. Len. B(arr. Temp. 2(j_u. Bound)) < = 0 Then. Bound = j_u. Bound - 1. Coerce. Lower. Bound - i_l.
Bound. j_n = Coerce. Lower. Bound - j_l. Bound. Re. Dim arr. Data(i_l. Bound + i_n To i_u. Bound + i_n, j_l. Bound + j_n To j_u.
Bound + j_n). ' As we've got the first row already.. For j = j_l. Bound To j_u. Bound. arr. Data(i_l. Bound + i_n, j + j_n) = arr. Temp. 2(j). For i = i_l. Bound + 1 To i_u. Bound Step 1. arr.
Temp. 2 = Split(arr. Temp. 1(i), Field.
Delimiter). For j = j_l. Bound To j_u. Bound Step 1.
Data(i + i_n, j + j_n) = arr. Temp. 2(j). Erase arr. Temp. 2. Erase arr. Temp. 1. Application. Status. Bar = False.
Join. 2d = arr. Data.