Import Data from Closed Workbook

[snigacookie]

Hi all,

I have hit a wall as to create an open event to import data from another workbook. This process has to be automated.

Any ideas on where to start?

 

[snigacookie]

I do not see how that will work according to the fact that the data is imported into an existing model.

The problem I have is that the source data can grow or shrink.

 

[erik.van.geit]

I do not see how that will work according to the fact that the data is imported into an existing model.

The problem I have is that the source data can grow or shrink.

can not help more without knowing what you want to do exactly
please provide an example & explain in more detail

 

[tusharm]

You have several logic and limit condition bugs.

First, you set temp1 and temp2 outside the While loop and never reevaluate them inside the loop. So, you are not really testing the values in the row that x is currently pointing to but are always testing row 2!

Second, your loop works while c<5. That means that when it terminates c will equal 5. But your very next statement checks if c>5 something that will always be false!

Third, the way you are handling c -- while the 1st bug is still present -- you will *always* process every record in the worksheet and then run into an Excel object error when you try and process a record beyond the worksheet's maximum.

Fourth, the way you are handling c -- and after you fix the 1st bug above -- it, i.e., c, will become 5 only when you encounter 5 consecutive source records where temp2="". But what if that never happens? You never check if you exhaust your data set. That means there is a possibility you will run beyond your data set -- as many as 5 records past the data set. That means there is a possibility, if your data set is large, to run out of rows in the worksheet.

Fifth, be careful on how you assign values to temp1 and temp2. You do not explicitly reference a workbook. And, you should.

All of the above said, you might want to look into treating your data source as a SQL source and use MS Query to retrieve the data. I am not sure how to frame the stopping criterion but I imagine it is possible. For an intro to the subject see
RDBMS in Excel
http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

This is what I currently have. Everything work but I do know know why i get an overflow error @ 'y=y+1'.

Sub Import_Removal_Data()
Dim wb As Workbook
Dim path As String
Dim x, y As Integer
Dim temp1, temp2 As Variant
 
Application.ScreenUpdating = False
path = "D:\Documents and Settings\xiae1703\Desktop\Project\"
Set wb = Workbooks.Open(path & "Fleet Hours and Cycles.xls", True, True)
'x is the source row
'y is the main data sheet row where it will be inputed
x = 2
y = 1
With thisworkbook.Worksheets(1)

temp1 = Worksheets(1).Cells(x, 1).Value
temp2 = Worksheets(1).Cells(x, 4).Value
 
c = 0
While c < 5

If temp1 <> "" Then
'Sets cells in main workbook equal to cells in other data workbook
thisworkbook.Worksheets(1).Cells(y, 1) = wb.Worksheets(1).Cells(x, 1)
thisworkbook.Worksheets(1).Cells(y, 2) = wb.Worksheets(1).Cells(x, 2)
y = y + 1
End If
x = x + 1
If temp2 = "" Then
    c = c + 1
Else
    c = 0
End If
If c > 5 Then wb.Worksheets(1).Activate

Wend
End With
wb.Close False
Set wb = Nothing
Application.ScreenUpdating = True
 
End Sub

 

[Fazza]

Further to tusharm's post. A query table can import from a closed workbook. You can set it to refresh on file opening. Whole thing happens without any worksheet formulas and with no VBA code. In fact, it can pull from multiple files if you like, and not just from Excel.

 

[snigacookie]

Thanks for your input. This is what I have done since then. I put the temp1, temp2 variables within the loop.

The code must end when c=5 and does not currently run into any errors.

However, I have come across the following scenario:

How do I add a line that seperates the data when c=2 so that it could seperate the data and put a table heading?

Sub Import_Removal_Data()
Dim wb As Workbook
Dim path As String
Dim x, y As Integer
Dim temp1 As String
Dim temp2 As String
Dim id, ac As Integer
 
 

Application.ScreenUpdating = False
path = "D:\Documents and Settings\xiae1703\Desktop\Backup\Project\"
Set wb = Workbooks.Open(path & "Fleet Hours and Cycles.xls", True, True)
'x is the source row
x = 2
'y is the main data sheet row
y = 2

temp1 = Worksheets(1).Cells(x, 1).Value
temp2 = Worksheets(1).Cells(x, 4).Value
 
c = 0
Do

If temp1 <> "" Then
'Sets cells in main workbook equal to cells in other data workbook
id = Worksheets(1).Cells(x, 1).Value
wb.Activate
Worksheets(1).Select
Cells(y, 1).Select
Cells(y, 1) = id
 

thisworkbook.Worksheets(1).Cells(y, 1) = wb.Worksheets(1).Cells(x, 1)
thisworkbook.Worksheets(1).Cells(y, 2) = wb.Worksheets(1).Cells(x, 2)
y = y + 1
End If
x = x + 1
temp1 = thisworkbook.Worksheets(1).Cells(x, 1).Value
temp2 = thisworkbook.Worksheets(1).Cells(x, 4).Value
If temp2 = "" Then
    c = c + 1
Else
    c = 0
End If

Loop Until c = 5
wb.Close False
Set wb = Nothing
Application.ScreenUpdating = True
 
End Sub

 

[snigacookie]

Currently, the data I am using to generate this worksheet has two parts that are seperated by two lines of space.

For instance, when doing a parts inventory.

Main section is OEM parts and the second contains aftermarket parts. I need the workbook to pull in both.

 

[Deathmac]

HiGuys,<o></o>
<o> </o>
I’mtrying to get data to my active workbook (0 New TP Routing Creation) from manydifferent closed workbooks via a selected path created by the below macro.<o></o>
<o> </o>
SubTPRoutingsData()<o></o>
<o> </o>
DimintChoice As Integer<o></o>
DimstrPath As String<o></o>
'onlyallow the user to select one file<o></o>
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect= False<o></o>
'makethe file dialog visible to the user<o></o>
intChoice= Application.FileDialog(msoFileDialogOpen).Show<o></o>
'determinewhat choice the user made<o></o>
IfintChoice <> 0 Then<o></o>
'getthe file path selected by the user<o></o>
strPath= Application.FileDialog( _<o></o>
msoFileDialogOpen).SelectedItems(1)<o></o>
'printthe file path to sheet 1<o></o>
Cells(2,10) = strPath<o></o>
EndIf<o></o>
<o> </o>
EndSub<o></o>
<o> </o>
Theabove works well (Show’s which file we are currently working from – as a check andplaces it into “J2”) but the next step is myproblem.<o></o>
<o> </o>
SubRetrievingData()<o></o>
<o></o>
DimobjWorkbook As Workbook<o></o>
Dimi As Integer<o></o>
'openthe workbook with data<o></o>
SetobjWorkbook = Workbooks.Open( _<o></o>
"Z:\15TP ROUTINGS\0 New TP Routing Creation\Information\J2")<o></o>
‘TubeAssembly - Part number<o></o>
ThisWorkbook.Activate<o></o>
'startin THIS workbook<o></o>
Sheets("Information").Select<o></o>
'switchto data import sheet<o></o>
'Openssource file (this filename never changes)<o></o>
Workbooks.OpenFilename:="Z:\15 TP ROUTINGS\0 New TP Routing Creation\Information\J2" ' ,ReadOnly:=True<o></o>
Sheets("Basicsheet").Select<o></o>
'switchto source data sheet<o></o>
[B7].CurrentRegion.Copy<o></o>
'Copydata to clipboard<o></o>
ThisWorkbook.Activate<o></o>
'Returnto THIS workbook<o></o>
[D5].PasteSpecialPaste:=xlPasteValues<o></o>
'pastedata to import start cell<o></o>
<o> </o>
Thisrepeats 22 more times to retrieve all data required and then “End Sub”<o></o>
<o> </o>
Pleasecan you assist? <o></o>
<o> </o>
Kindregards<o></o>
Tony<o></o>

 

[Deathmac]

The spaces have gone haywire, sorry

 

[erik.van.geit]

Hi, you replied to a post from 5 years ago. You would better start your own thread. You might refer to this one with a link.
Currently I'm only quickly visiting here: no time to reply myself.
kind regards,
Erik

 

[Deathmac]

Battled to see where to start. Andrew is helping me, thanks