Hi - You guys helped me out before and I'm hoping someone can give me a little advice with this problem. Here's code to import data from three url's, results of a search query in a web database. This is no big deal with 3 url's, contained in cells A1,B1,C1. Problem is I have searches that have over 8000 url's so I need to convert this to a loop. I'm not sure if it's best to use a For/next or an If then (like If activecell <> "" as it tabs across the list). With either method, I really need help creating and stepping the variables needed to keep pasting the imported data straight down the page. I would most greatly appreciate any help you guys can offer. Thanks!
Sub import2() ' ' import2 Macro ' ' Range("A1").Select ActiveCell.FormulaR1C1 = _ "http://php.app.com/fed_employees10/results.php?agency_name=%25&statename=Maryland&countyname=Calvert+County&job_title=%25&tfm_order=ASC&tfm_orderby=fullname" With ActiveCell.Characters(Start:=1, Length:=8).Font .Name = "Calibri" .FontStyle = "Regular" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontMinor End With With ActiveCell.Characters(Start:=9, Length:=148).Font .Name = "Calibri" .FontStyle = "Bold" .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontMinor End With Range("B1").Select With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://php.app.com/fed_employees10/results.php?agency_name=%25&statename=Maryland&countyname=Calvert+County&job_title=%25&tfm_order=ASC&tfm_orderby=fullname" _ , Destination:=Range("$A$2")) .Name = _ "results.php?agency_name=%25&statename=Maryland&countyname=Calvert+County&job_title=%25&tfm_order=ASC&tfm_orderby=fullname" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "1" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Range("B1").Select ActiveCell.FormulaR1C1 = _ "http://php.app.com/fed_employees10/results.php?pageNum_Recordset1=1&totalRows_Recordset1=29&agency_name=%25&statename=Maryland&countyname=Calvert+County&job_title=%25&tfm_order=ASC&tfm_orderby=fullname" Range("C1").Select With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://php.app.com/fed_employees10/results.php?pageNum_Recordset1=1&totalRows_Recordset1=29&agency_name=%25&statename=Maryland&countyname=Calvert+County&job_title=%25&tfm_order=ASC&tfm_orderby=fullname" _ , Destination:=Range("$A$15")) .Name = _ "results.php?pageNum_Recordset1=1&totalRows_Recordset1=29&agency_name=%25&statename=Maryland&countyname=Calvert+County&job_title=%25&tfm_order=ASC&tfm_orderby=fullname" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "1" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Range("C1").Select ActiveCell.FormulaR1C1 = _ "http://php.app.com/fed_employees10/results.php?pageNum_Recordset1=2&totalRows_Recordset1=29&agency_name=%25&statename=Maryland&countyname=Calvert+County&job_title=%25&tfm_order=ASC&tfm_orderby=fullname" Range("A28").Select With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://php.app.com/fed_employees10/results.php?pageNum_Recordset1=2&totalRows_Recordset1=29&agency_name=%25&statename=Maryland&countyname=Calvert+County&job_title=%25&tfm_order=ASC&tfm_orderby=fullname" _ , Destination:=Range("$A$28")) .Name = _ "results.php?pageNum_Recordset1=2&totalRows_Recordset1=29&agency_name=%25&statename=Maryland&countyname=Calvert+County&job_title=%25&tfm_order=ASC&tfm_orderby=fullname" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "1" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub
Bookmarks