Importing spreadsheets
 

How can I import an Excel spreadsheet from the web into a current worksheet?

We have a company intranet with a link to an Excel spreadsheet that is
updated often. I would like to create a macro to go out to the site and pull
the spreadsheet into one of my spreasheets.

Any help would be great.

Thanks
Mike

Importing spreadsheets
 

Mike:

You Can Downlond This Workbook and copy Sheet

example:

'Module
Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, ByVal strURL As String, _
ByVal strFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Sub DownFile()
Dim lReturn As Long
Dim URL As String
Dim fname As String
Dim wbk As Workbook
URL = "http://vba.com.tw/file/Book1a.xls"
'Workbook must save
fname = ThisWorkbook.Path & "\temp.xls"
lReturn = URLDownloadToFile(0, URL, fname, 0, 0)
If lReturn < 0 Then Call MsgBox("connect error"): Exit Sub
Set wbk = Workbooks.Open(fname)
wbk.Worksheets("Sheet1").Copy After:= _
ThisWorkbook.Worksheets("Sheet1")
wbk.Close SaveChanges:=False
Kill fname
End Sub

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Mike" wrote:

How can I import an Excel spreadsheet from the web into a current worksheet?

We have a company intranet with a link to an Excel spreadsheet that is
updated often. I would like to create a macro to go out to the site and pull
the spreadsheet into one of my spreasheets.

Any help would be great.

Thanks
Mike

Importing spreadsheets
 

When i run it this is what comes up in "debug" (see yellow)

'Module
Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, ByVal strURL As String, _
ByVal strFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Sub DownFile()
Dim lReturn As Long
Dim URL As String
Dim fname As String
Dim wbk As Workbook
URL =
"http://portico.corp.erac.com:7778/pls/portal/frame01.efqi_survey/region data
export (excel)"
'Workbook must save
fname = ThisWorkbook.Path & "\temp.xls"
lReturn = URLDownloadToFile(0, URL, fname, 0, 0)
If lReturn < 0 Then Call MsgBox("connect error"): Exit Sub
Set wbk = Workbooks.Open(fname)
wbk.Worksheets("EFQI VIEW 1").Copy After:= _ <-------yellow
ThisWorkbook.Worksheets("EFQI VIEW 1") <-------yellow
wbk.Close SaveChanges:=False
Kill fname
End Sub

"chijanzen" wrote:

Mike:

You Can Downlond This Workbook and copy Sheet

example:

'Module
Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, ByVal strURL As String, _
ByVal strFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Sub DownFile()
Dim lReturn As Long
Dim URL As String
Dim fname As String
Dim wbk As Workbook
URL = "http://vba.com.tw/file/Book1a.xls"
'Workbook must save
fname = ThisWorkbook.Path & "\temp.xls"
lReturn = URLDownloadToFile(0, URL, fname, 0, 0)
If lReturn < 0 Then Call MsgBox("connect error"): Exit Sub
Set wbk = Workbooks.Open(fname)
wbk.Worksheets("Sheet1").Copy After:= _
ThisWorkbook.Worksheets("Sheet1")
wbk.Close SaveChanges:=False
Kill fname
End Sub

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Mike" wrote:

How can I import an Excel spreadsheet from the web into a current worksheet?

We have a company intranet with a link to an Excel spreadsheet that is
updated often. I would like to create a macro to go out to the site and pull
the spreadsheet into one of my spreasheets.

Any help would be great.

Thanks
Mike