Well it looks like plenty of people are interested in the solution, but I got no replies on ideas so I decided to follow up and post what I came up with. If anyone can come up with a better mousetrap, please feel free to add to this post
STEP 1
You'll need at least 2 worksheets. One I called Commands and the other I called DATA
STEP 2
I assigned a macro I found from other a post that pulls an actual file like an excel or word document from a link I declare (internal or external) and saves it to my hard drive. All the other codes took data from an HTML page. Code below:
Sub DownloadFile()
Dim FileNum As Long
Dim FileData() As Byte
Dim MyFile As String
Dim WHTTP As Object
On Error Resume Next
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
If Err.Number <> 0 Then
Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
End If
On Error GoTo 0
MyFile = "http://www.YOUR_LINK_HERE.xls"
WHTTP.Open "GET", MyFile, False
WHTTP.Send
FileData = WHTTP.ResponseBody
Set WHTTP = Nothing
If Dir("C:\MyDownloads", vbDirectory) = Empty Then MkDir "C:\MyDownloads"
FileNum = FreeFile
Open "C:\MyDownloads\YOUR_FILE_NAME HERE.xls" For Binary Access Write As #FileNum
Put #FileNum, 1, FileData
Close #FileNum
MsgBox "Your file has been successfully downloaded [C:\MyDownloads\...]!"
End Sub
STEP 3
Created another button on my Commands worksheet and assigned it to a code that would open the downloaded file, copy a declared range and paste it to my working workbook (DATA). The code is 2 parts: OpenFile1 and OpenFile 2. This code only pulls in Excel files. Here's the code I found from another post:
Sub OpenFile1()
' Opens the Workbook that you downloaded in STEP 2 if closed
On Error GoTo OpenWorkBook:
Dim BookName As String
Workbooks("NAME_OF_FILE_YOU_DOWNLOADED_NO_EXTENSION").Activate
OpenFile2
Exit Sub
OpenWorkBook:
If Err.Number = 9 Then
Workbooks.Open Filename:="C:\MyDownloads\NAME_OF_FILE.xls"
Resume
End If
OpenFile2
End Sub
Sub OpenFile2()
' Copies the data from a specified range and pastes it in working file
Application.ScreenUpdating = False
Workbooks("FILE_NAME_YOU_DOWNLOADED_NO_EXTENTION").Activate
' Add Sheets("Sheet1").Select if you need to select a specific worksheet
' The code below selects a range and everything down assuming no blanks in the data
' You can change the range as you see fit
Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
' Enter the name of your working file below without the file extention
Workbooks("NAME_OF_WORKING_FILE_NO_EXTENTION").Activate
' This is the name of the worksheet where you want your data pasted into
Sheets("DATA").Select
' Clears any previous data that was there
' You can change the range as you see fit
If Range("A1") <> ("") Then
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Workbooks("vfsinventory").Activate
Range("A1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
' Saves and closes Downloaded file you copied from
Workbooks("NAME_OF_DOWNLOADED_FILE.xls").Close SaveChanges:=True
End Sub
STEP 4
For most, Steps 1-3 will do the trick, but I had an added issue. The data I copy is in 2 columns: Name and Qty. The Name column actually contains delimited data which also includes titles and subtitles which were all assigned to a "0" Qty. We're talking over 25,000 rows unless! So I needed to automate the TEXT-TO-COLUMN process and delete the titles and subtitles so I could filter the data. My other issue was that a certain name I used to filter my data on only had 4 fields instead of 5 like the others. So I RECORDED A MACRO to...
- Move Qty over to a new column
- Perform TEXT-TO-COLUMN (or delimit the text)
- Select the data and turn on filtering
- Filter out the 4 field item; cut it, and paste it to a new worksheet
- Go back and refilter the data
- Delete the blanks, etc...
- And clean it up a bit.
I then created a button on the DATA form and assigned it to the macro I just recorded.
So that's what I did. I'm sure you can combine the 3 codes into one, but I got lazy and...well...buttons are just fun to push! :D
Oh! An added tip! I uploaded to my WindowsLIVE account and just keep it stored there. So now it runs off my computer but saves back out to the net for easy access where ever I may be. I'm constantly having to access this file, so having access to it like that is extremely helpful!
