Astley has attended:
Access Advanced course
Creating an automated macro that autopopulates a worksheet from
I'm trying to creating a command macro coded button on a spreadsheet (excel 2007) that retrieves and auto- populates a worksheet of data stored in a csv file format located on a F:Drive on a directory. Is this possible to do? If so, do you have any ideas how this can be done?
Ultimately the user will just click on the command button to display the data rather than go through various steps to the directory in order to retrieve the data.
Thanks for your contribution
RE: Creating an automated macro that autopopulates a worksheet f
Hi Astley,
Thank you for your question.
Please find the VBA code below:
------------------------------------------------------------------------
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\Simon\Desktop\Carhires.csv", Destination:=Range("a1"))
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
---------------------------------------------------------------------------
This code might not be the most effective and possibly not the quickest way of doing it but it has worked for a friend in the past. This will connect to a CSV file and dump the data into cell A1 of the active sheet.
1. Go to the worksheet and select the Developer Tab.
2. If the Developer tab is not present then go to Office button and click the developer checkbox on the popular options.
3. Click the Insert button on the Controls group.
4. Select the Active X Command Button and draw a button out on the sheet.
5. Right click over the button and choose View Code.
6. Within the Sub and End Sub copy the code above and adjust it with the relevant path.
7. Then switch off Design Mode and test the button.
I hope this answers your question.
Regards
Simon
