Clash Royale CLAN TAG#URR8PPP

Import column from excel file with path into array

Is there a faster way to import a column from a specific excel file as an array with VBA?

The code that I'm currently using has to open the excel file. Is there a way to do this in the background? Is there a way to read values row by row from the first column?

Thanks

My code below:

Sub LoadExcelArray()

Dim Vendor As Variant
Dim wb As Workbook
Dim sFile As String
sFile = "D:Desktoptest.xlsx"

Application.ScreenUpdating = False

Set wb = Application.Workbooks.Open(sFile)

Vendor = wb.Sheets(1).Range("A1:A95").Value2

wb.Close False

Application.ScreenUpdating = True

MsgBox Vendor(30, 1)

End Sub


1 Answer
1

What you're already using is the best way in my opinion. But if you're looking for other options: The xlsx file is actually a zip file. You could open it as a zip file and extract the contents. This can be done without starting Excel. So it should be faster.

https://msdn.microsoft.com/en-us/library/dd922181(v=office.12).aspx

You can save the spreadsheet as a text file or you can save it as a CSV file. The data will look slightly different between those two options so use whatever works for you.

If your data will never have tabs in it, then tab delimited(.txt) is the way to go. Open the file, split by rows and then split by columns. Done.

Split

CSV

xlsx

Open For Input

.txt

Open for Input

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.