Link to home
Start Free TrialLog in
Avatar of AFGPHXExcel
AFGPHXExcel

asked on

Import data from one workbook to another

I have a database that is suppose to import data from spreadsheets submitted. The database goes through two different worksheets in the imported workbook and identifies if the data has changed in a specific row by a change flag, the letter "Y". If the data has changed it is then copied and pasted into the corresponding row in the original database. For some reason, I can get excel to identify the import workbook but it is not recognizing the change flag and therefore not copying and pasting the data I need. The code is as follows:

' Import Data Button
Private Sub CommandButton10_Click()

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With

' Browse to open a new excel file
    Dim import_file_location As Variant
        import_file_location = file_open()

If import_file_location = False Then
    Exit Sub
   
ElseIf VBA.InStr(import_file_location, "TF_") = 0 Then
    MsgBox ("Please select a QAR/GTPR Service Tracker to import")
    Exit Sub

Else

    With ThisWorkbook
        ' Get this workbook's name
            Dim full_master_name As String
                full_master_name = .Name
    End With
   
    Dim del_start As Long
    Dim full_import_file_name As String
    Dim import_file_name As String
   
        del_start = VBA.InStrRev(import_file_location, "\")
        del_start = VBA.Len(import_file_location) - del_start
        full_import_file_name = VBA.Right(import_file_location, del_start)
       
        del_start = VBA.InStrRev(full_import_file_name, ".")
        import_file_name = VBA.Left(full_import_file_name, del_start - 1)

    ' Open the new file under the new name
        Dim XL As Excel.Application
        Set XL = New Excel.Application
            XL.Workbooks.Open Filename:=import_file_location
 
    With XL.Workbooks(full_import_file_name)
    .Activate
    ' Find the cells in the Risk and Trend Analysis Tab to Copy over
        With Sheet4
            .Activate
            .Select
            If .AutoFilterMode = True Then
                If .FilterMode = True Then
                    .ShowAllData
                End If
            End If
         
            ' Define import range
                Dim import_header_row As Long
                Dim import_first_row As Long
                Dim import_last_row As Long
                Dim import_first_col As Long
                Dim import_last_col As Long
                    import_header_row = 4
                    import_first_row = import_header_row + 1
                    import_last_row = .UsedRange.Rows.Count
                    import_first_col = 1
                    import_last_col = .UsedRange.Columns.Count
           
                Dim RT_flag As Range
                Dim import_copy_row As Long
                Dim master_paste_row As Long
                    Dim prop_risk_like As String
                    Dim KTR_CARs As String
                    Dim pers_change As String
                    Dim pop_change As String
                    Dim best_practices As String
                    Dim notes_info As String
                        Dim import_site_code As String
                        Dim import_service_type As String
                        Dim import_service_name As String
                        Dim import_sub_loc As String
                            Dim found_site_code As Range
                            Dim found_service_type As String
                            Dim found_service_name As String
                            Dim found_sub_loc As String
                                Dim first_addr As Variant
                                Dim audit_idx As Long
                       
                ' Cycle through the QAR Change Flag column in the Risk and Trend Analysis Tab
                    For Each RT_flag In .Range(.Cells(import_first_row, .Range("RT_change_flag").Column), _
                                               .Cells(import_last_row, .Range("RT_change_flag").Column))
                        If RT_flag = "Y" Then
                           
                            ' Get Row information before finding if it's a Key Service
                                import_copy_row = RT_flag.Row
                               
                            ' Get Risk information to copy over
                                prop_risk_like = .Range(.Cells(import_copy_row, .Range("RT_prop_risk_like").Column), _
                                                        .Cells(import_copy_row, .Range("RT_prop_risk_like").Column)).Value
                                KTR_CARs = .Range(.Cells(import_copy_row, .Range("RT_KTR_CARs").Column), _
                                                  .Cells(import_copy_row, .Range("RT_KTR_CARs").Column)).Value
                                pers_change = .Range(.Cells(import_copy_row, .Range("RT_personnel").Column), _
                                                          .Cells(import_copy_row, .Range("RT_personnel").Column)).Value
                                pop_change = .Range(.Cells(import_copy_row, .Range("RT_population").Column), _
                                                    .Cells(import_copy_row, .Range("RT_population").Column)).Value
                                best_practices = .Range(.Cells(import_copy_row, .Range("RT_KTR_best_prac").Column), _
                                                        .Cells(import_copy_row, .Range("RT_KTR_best_prac").Column)).Value
                                notes_info = .Range(.Cells(import_copy_row, .Range("RT_notes").Column), _
                                                    .Cells(import_copy_row, .Range("RT_notes").Column)).Value
                                                   
                            ' Get row info to perform the find function
                                import_site_code = .Range(.Cells(import_copy_row, .Range("RT_site_number").Column), _
                                                          .Cells(import_copy_row, .Range("RT_site_number").Column)).Value
                                import_service_type = .Range(.Cells(import_copy_row, .Range("RT_service_type").Column), _
                                                             .Cells(import_copy_row, .Range("RT_service_type").Column)).Value
                                import_service_name = .Range(.Cells(import_copy_row, .Range("RT_service").Column), _
                                                             .Cells(import_copy_row, .Range("RT_service").Column)).Value
                                import_sub_loc = .Range(.Cells(import_copy_row, .Range("RT_sub_loc").Column), _
                                                        .Cells(import_copy_row, .Range("RT_sub_loc").Column)).Value
                                                 
                            ' Find the same row in the Master workbook
                                With Workbooks(full_master_name)
                                    With Sheet4
                                        If .FilterMode = True Then
                                            .ShowAllData
                                        End If
                                       
                                        Set found_site_code = .Range("RT_site_number").Find(What:=import_site_code, _
                                                                                        LookIn:=xlFormulas, _
                                                                                        Lookat:=xlWhole, _
                                                                                        SearchOrder:=xlByRows, _
                                                                                        SearchDirection:=xlNext, _
                                                                                        SearchFormat:=False, _
                                                                                        MatchCase:=False)
                                           
                                            ' Get the address of the first found site to keep from infinite looping
                                                If Not found_site_code Is Nothing Then
                                                    first_addr = found_site_code.Address
                                                End If
                                               
                                                    audit_idx = 0
                                                Do Until audit_idx = 1 Or audit_idx = 2
                                                       
                                                    If found_site_code Is Nothing Then
                                                        audit_idx = 2
                                                    End If
                                                   
                                                    ' Get found row info and found row values
                                                        master_paste_row = found_site_code.Row
                                                       
                                                        found_service_type = .Range(.Cells(master_paste_row, .Range("RT_service_type").Column), _
                                                                                    .Cells(master_paste_row, .Range("RT_service_type").Column)).Value
                                                        found_service_name = .Range(.Cells(master_paste_row, .Range("RT_service").Column), _
                                                                                    .Cells(master_paste_row, .Range("RT_service").Column)).Value
                                                        found_sub_loc = .Range(.Cells(master_paste_row, .Range("RT_sub_loc").Column), _
                                                                               .Cells(master_paste_row, .Range("RT_sub_loc").Column)).Value
                               
                                                    ' Stop when all found values equal the search values
                                                        If found_service_type = import_service_type And found_service_name = import_service_name _
                                                                        And found_sub_loc = import_sub_loc Or found_site_code.Address = first_addr Then
                                                            audit_idx = 1
                                                        End If
                                         
                                                    Set found_site_code = .Range("RT_site_number").FindNext(found_site_code)
                                               
                                                Loop
                                   
                                                ' Copy the information over to the Risk & Trend Analysis Tab when a match is found
                                                    If audit_idx = 1 Then
                                                        .Range(.Cells(master_paste_row, .Range("RT_prop_risk_like").Column), _
                                                               .Cells(master_paste_row, .Range("RT_prop_risk_like").Column)).Value = prop_risk_like
                                                        .Range(.Cells(master_paste_row, .Range("RT_KTR_CARs").Column), _
                                                               .Cells(master_paste_row, .Range("RT_KTR_CARs").Column)).Value = KTR_CARs
                                                        .Range(.Cells(master_paste_row, .Range("RT_personnel").Column), _
                                                               .Cells(master_paste_row, .Range("RT_personnel").Column)).Value = pers_change
                                                        .Range(.Cells(master_paste_row, .Range("RT_population").Column), _
                                                               .Cells(master_paste_row, .Range("RT_population").Column)).Value = pop_change
                                                        .Range(.Cells(master_paste_row, .Range("RT_KTR_best_prac").Column), _
                                                               .Cells(master_paste_row, .Range("RT_KTR_best_prac").Column)).Value = best_practices
                                                        .Range(.Cells(master_paste_row, .Range("RT_notes").Column), _
                                                               .Cells(master_paste_row, .Range("RT_notes").Column)).Value = notes_info
                                                    End If
                                   
                                    End With
                                End With
                        End If
                   
                    Next RT_flag
        End With
   
    ' Find the cells to copy over from the "Service Tracker Tab"
        With Sheet2
            .Activate
            If .FilterMode = True Then
                .ShowAllData
            End If
           
            ' Cycle through the QAR Change Flag column in the Service tracker Tab
                Dim ST_flag As Range
                Dim PCOR_name As String
                Dim PCOR_redeploy As String
                Dim PCOR_email As String
                Dim current_QAR As String
                Dim next_QAR As String
                    Dim import_surv_type As String
                    Dim import_audit_type As String
                    Dim found_surv_type As String
                    Dim found_audit_type As String
                   
                    import_first_row = 3
                    import_last_row = .UsedRange.Rows.Count
                    import_last_col = .UsedRange.Columns.Count
                   
                    For Each ST_flag In .Range(.Cells(import_first_row, .Range("ST_change_flag").Column), _
                                               .Cells(import_last_row, .Range("ST_change_flag").Column))
                        If ST_flag = "Y" Then
                           
                            ' Get Row information before finding if it's a Key Service
                                import_copy_row = ST_flag.Row
                               
                            ' Get QAR and COR information to copy over
                                PCOR_name = .Range(.Cells(import_copy_row, .Range("ST_PCOR_name").Column), _
                                                   .Cells(import_copy_row, .Range("ST_PCOR_name").Column)).Value
                                PCOR_redeploy = .Range(.Cells(import_copy_row, .Range("ST_PCOR_R_R").Column), _
                                                       .Cells(import_copy_row, .Range("ST_PCOR_R_R").Column)).Value
                                PCOR_email = .Range(.Cells(import_copy_row, .Range("ST_PCOR_email").Column), _
                                                    .Cells(import_copy_row, .Range("ST_PCOR_email").Column)).Value
                                current_QAR = .Range(.Cells(import_copy_row, .Range("ST_current_QAR").Column), _
                                                     .Cells(import_copy_row, .Range("ST_current_QAR").Column)).Value
                                next_QAR = .Range(.Cells(import_copy_row, .Range("ST_next_QAR").Column), _
                                                  .Cells(import_copy_row, .Range("ST_next_QAR").Column)).Value
                                                   
                            ' Get row info to perform the find function
                                import_surv_type = .Range(.Cells(import_copy_row, .Range("ST_surv_type").Column), _
                                                          .Cells(import_copy_row, .Range("ST_surv_type").Column)).Value
                                import_audit_type = .Range(.Cells(import_copy_row, .Range("ST_audit_type").Column), _
                                                           .Cells(import_copy_row, .Range("ST_audit_type").Column)).Value
                                import_site_code = .Range(.Cells(import_copy_row, .Range("ST_site_number").Column), _
                                                          .Cells(import_copy_row, .Range("ST_site_number").Column)).Value
                                import_service_type = .Range(.Cells(import_copy_row, .Range("ST_service_type").Column), _
                                                             .Cells(import_copy_row, .Range("ST_service_type").Column)).Value
                                import_service_name = .Range(.Cells(import_copy_row, .Range("ST_service").Column), _
                                                             .Cells(import_copy_row, .Range("ST_service").Column)).Value
                                import_sub_loc = .Range(.Cells(import_copy_row, .Range("ST_sub_loc").Column), _
                                                        .Cells(import_copy_row, .Range("ST_sub_loc").Column)).Value
                                                 
                            ' Find the same row in the Master workbook
                                With Workbooks(full_master_name)
                                    With Sheet2
                                        .Activate
                                        If .FilterMode = True Then
                                            .ShowAllData
                                        End If
                                       
                                        Set found_site_code = .Range("ST_site_number").Find(What:=import_site_code, _
                                                                                        LookIn:=xlFormulas, _
                                                                                        Lookat:=xlWhole, _
                                                                                        SearchOrder:=xlByRows, _
                                                                                        SearchDirection:=xlNext, _
                                                                                        SearchFormat:=False, _
                                                                                        MatchCase:=False)
                                           
                                            ' Get the address of the first found site to keep from infinite looping
                                                If Not found_site_code Is Nothing Then
                                                    first_addr = found_site_code.Address
                                                End If
                                               
                                                    audit_idx = 0
                                                Do Until audit_idx = 1 Or audit_idx = 2
                                                   
                                                    If found_site_code Is Nothing Then
                                                        audit_idx = 2
                                                    End If
                                                   
                                                    ' Get found row info and found row values
                                                        master_paste_row = found_site_code.Row
                                                       
                                                        found_surv_type = .Range(.Cells(master_paste_row, .Range("ST_surv_type").Column), _
                                                                                 .Cells(master_paste_row, .Range("ST_surv_type").Column)).Value
                                                        found_audit_type = .Range(.Cells(master_paste_row, .Range("ST_audit_type").Column), _
                                                                                  .Cells(master_paste_row, .Range("ST_audit_type").Column)).Value
                                                        found_service_type = .Range(.Cells(master_paste_row, .Range("ST_service_type").Column), _
                                                                                    .Cells(master_paste_row, .Range("ST_service_type").Column)).Value
                                                        found_service_name = .Range(.Cells(master_paste_row, .Range("ST_service").Column), _
                                                                                    .Cells(master_paste_row, .Range("ST_service").Column)).Value
                                                        found_sub_loc = .Range(.Cells(master_paste_row, .Range("ST_sub_loc").Column), _
                                                                               .Cells(master_paste_row, .Range("ST_sub_loc").Column)).Value
                               
                                                    ' Stop when all found values equal the search values
                                                        If found_service_type = import_service_type And found_service_name = import_service_name _
                                                                        And found_sub_loc = import_sub_loc And found_surv_type = import_surv_type _
                                                                        And found_audit_type = import_audit_type Or found_site_code.Address = first_addr Then
                                                            audit_idx = 1
                                                        End If
                                         
                                                    Set found_site_code = .Range("ST_site_number").FindNext(found_site_code)
                                               
                                                Loop
                                   
                                                ' Copy the information over to the Risk & Trend Analysis Tab when a match is found
                                                    If audit_idx = 1 Then
                                                        .Range(.Cells(master_paste_row, .Range("ST_PCOR_name").Column), _
                                                               .Cells(master_paste_row, .Range("ST_PCOR_name").Column)).Value = PCOR_name
                                                        .Range(.Cells(master_paste_row, .Range("ST_PCOR_R_R").Column), _
                                                               .Cells(master_paste_row, .Range("ST_PCOR_R_R").Column)).Value = PCOR_redeploy
                                                        .Range(.Cells(master_paste_row, .Range("ST_PCOR_email").Column), _
                                                               .Cells(master_paste_row, .Range("ST_PCOR_email").Column)).Value = PCOR_email
                                                        .Range(.Cells(master_paste_row, .Range("ST_current_QAR").Column), _
                                                               .Cells(master_paste_row, .Range("ST_current_QAR").Column)).Value = current_QAR
                                                        .Range(.Cells(master_paste_row, .Range("ST_next_QAR").Column), _
                                                               .Cells(master_paste_row, .Range("ST_next_QAR").Column)).Value = next_QAR
                                                       
                                                    End If
                                   
                                    End With
                                End With
                        End If
                   
                    Next ST_flag
        End With
   
        With Sheet4
            .Activate
        End With
       
    .Close
    End With

    MsgBox ("All QAR/GTPR data has been imported")
   
End If

' Turn application alerts back on
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With

End Sub

Any help would be much appreciated! Thanks!
Avatar of redmondb
redmondb
Flag of Afghanistan image
AFGPHXExcel,

Add a Breakpoint at...
  
RT_flag = "Y"

Open in new window

Assuming it's triggered, run the following in the Immediate pane...
  
?.Range(.Cells(import_first_row, .Range("RT_change_flag").Column), .Cells(import_last_row, .Range("RT_change_flag").Column))

Open in new window

Does it return the correct range?

Finally, although I don't think it's relevant to your current problem, I notice that you use UsedRange to determine the last row and column. This is risky behaviour as UsedRange excludes initial blank rows and/or columns.

Regards,
Brian.
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

It has the correct range. When I reference the import file, the file does not appear. Is there a way to have the file open and could this be an issue?
Avatar of redmondb
redmondb
Flag of Afghanistan image
AFGPHXExcel,

Possibly! How are you trying to reference it?

What is "Sheet4"?

Thanks,
Brian.
Avatar of redmondb
redmondb
Flag of Afghanistan image
AFGPHXExcel,

Pending your answers, a suggestion - turn off DisplayAlerts to see if you get any unexpected Alerts.

Also, why are you creating another Excel application? And what's with the nested With's?

Thanks,
Brian.
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

Sheet4 was originially referenced .Sheets("Risk and Trend Analysis") but since this is being sent out I have run into issues with tab name changes.
Avatar of redmondb
redmondb
Flag of Afghanistan image
AFGPHXExcel,

Thanks!

This is going quite slowly, so it would be good if you could upload a copy of the files obviously with sensitive information changed.

Regards,
Brian.
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

Ok here the two files. The North Tracker is the original database and TF Duke is the file to be imported. The importing is to be done on the Risk and Trend sheet.
2011-06-North-Service-Tracker-Sa.xlsm
2011-06-15-TF-Duke-Risk-Analysis.xlsm
Avatar of redmondb
redmondb
Flag of Afghanistan image
AFGPHXExcel,

Thanks, that's excellent!

I've run it a couple of times and I may have spotted a small problem. I'll update in a few minutes.

Regards,
Brian.
Avatar of redmondb
redmondb
Flag of Afghanistan image
AFGPHXExcel,

"not copying and pasting the data I need" - can you give me a single example of one bit of information that's not being pasted (i.e. a cell that's not been changed, but should have been - the original value and the  value it should be).

Thanks,
Brian.
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

Yes, from the TF Duke  "Risk and Trend" sheet the proposed risk column values are to copy over to the North Tracker under the same rows and replace the value there. Also the value in columns AY through BE are to copy over also.
Avatar of redmondb
redmondb
Flag of Afghanistan image
AFGPHXExcel,

When I run it, AY367:BC367 are updating...

AY367:BB367 = "None."
BC367              = "Based on trend analysis, and a lack of CARs being written against this service, recommend the risk be lowered. "

Is this not happening for you?

Regards,
Brian.
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

No when I click the import button on the Risk & Trend sheet and select the file, I recieved an error on this :

With .Sheets("Risk & Trend Analysis")
            If .AutoFilterMode = True Then
                If .FilterMode = True Then
                    .ShowAllData
                End If
            End If

Its a showalldata method of worksheet class failed error.
Avatar of redmondb
redmondb
Flag of Afghanistan image
AFGPHXExcel,

(1) Were you always getting this message? I asked you yesterday to drop the DisplayAlerts, but you didn't responded - had you dropped it before the error run?

(2) The macro seems to be running perfectly for me and giving lots of errors for you. It's very unlikely that it's a version difference (I'm on 2010) so I'm looking at it being something else interfering (e.g. an AddIn on your machine) or some difference in the way we're running it. Please try the following...
(A) If you had dropped the DisplayAlerts line in "2011-06_North_Service_Tracker_Sa.xlsm", please restore it. Save the file.
(B) Restart Excel.
(C) Load "2011-06_North_Service_Tracker_Sa.xlsm". Confirm that AY367 is blank.
(D) Click on the Import button.
(E) Save "2011-06-15-TF_Duke_Risk_Analysis_Tracker.xlsm" when prompted.
(F) Check value of AY367.

Let me know what happens - particularly if you get any messages!

I'm afraid I'll be away for the next 3 hours, or so I'll check in as soon as I get back.

Thanks,
Brian.
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

Will do Brian. Thanks for the help. I am halfway around the world so its getting to be night time here. I may not be back in until tomorrow morning (my time).

The version I sanitized was an older version, and the Sheet 4 name went back to .sheets("risk and trend")
Avatar of redmondb
redmondb
Flag of Afghanistan image
AFGPHXExcel,

OK, I'm back, too late for you, I'm sure. (It's now 17:55 my time. What's yours?)

Please, please make sure you're using the two files you posted above!

Regards,
Brian
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

Brian,

I went through the steps as instructed and I came back to the showall data error again. When I rename the sheets by their numbers ie. Sheet4, it seems to just skip the reference and does not import anything. I am currently using Excel 2007 as an information add in.

Thanks,
Marc
Avatar of redmondb
redmondb
Flag of Afghanistan image
Marc,

Are you absolutely sure that you're using the version you uploaded?

I don't understand "When I rename the sheets by their numbers"? When/why are you renaming sheets??

Regards,
Brian.
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

VBA references sheets as Sheet4("Risk & Trend Analysis"). When I ran into the error using  With .sheets("Risk & Trend Analysis"), I tried referencing the sheet by using With Sheet4. This did not produce the error but it seems like it is also not being recognized correctly.
Avatar of redmondb
redmondb
Flag of Afghanistan image
Marc,

Please download the files in 35988082 above - careful, you'll need to rename them changing "_" to "-". Then run the steps in 35988907.

Thanks,
Brian.
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

Using the files that I uploaded (I downloaded them from the link) and following the steps from above, I click the Import button select the file I downloaded and then it gives me the showalldata error.
Avatar of redmondb
redmondb
Flag of Afghanistan image
Marc,

Thanks! I'm going to do exactly the same, in case I inadvertently changed something. Back in a minute.

Regards,
Brian.
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

I appreciate the help! I bet this is a little frustrating haha.
Avatar of redmondb
redmondb
Flag of Afghanistan image
Marc,

No more than for yourself!

OK, ran without problem. So...

(1) I have attached the updated files from my, hopefully, successful run. Please check that the results are correct.

(2) In the meantime, I'm changing your file to hard code the file name. I'll copy it up in a few minutes.

Thanks,
Brian.

2011-06-NS-Tracker-Sa-Updated.xlsm

2011-06-15-TF--Tracker-Updated.xlsm
Avatar of redmondb
redmondb
Flag of Afghanistan image
Marc,

OK, please repeat 35988907 with the attached. The changes are that I renamed the files (Experts-Exchange interferes with file names), updated the code to reflect this and added a check to make sure that the correct TF file has been opened.

Regards,
Brian.

North-Service-Tracker-Sa.xlsm

Duke-TF-Tracker.xlsm
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

I tested the two files and they work great together. I then took the code from file that was attached put it into the original database and tried to import the Duke file. No go due to .showalldata error, but if I import the file you just uploaded it takes it. Doesn't make sense why it would take the new import file over the old one.
Avatar of redmondb
redmondb
Flag of Afghanistan image
AFGPHXExcel,

I suspect that at least one problem is an issue about versions, hence the value of the rename.

As I understand, you took the code from North-Service-Tracker-Sa.xlsm and put it in your "North_Service". You then ran the import from that using Duke-TF-Tracker.xlsm and got the ShoWall error. Correct?

Ah, it just struck me where the ShowAll is probably coming from and it is a version issue. You're protecting structure in the files. The code for this is in  ThisWorkBook macros - I'll bet you forgot to include these when you imported my code.

Is that correct?

Regards,
Brian.
Avatar of redmondb
redmondb
Flag of Afghanistan image
Marc,

While I'm waiting, earlier you said "When I reference the import file, the file does not appear." I presume you were talking about the fact that you couldn't see the "TF" file. The reason for this is that the import macro starts a whole new hidden instance of Excel and opens the "TF" file in that. In principle, there's nothing wrong with this but it's an extra overhead, it's something you need to be aware of when writing code to access that file and it means the "Save" message for the "TF" file will probably be hidden behind other windows making it seem as if the import has hung.

Why are you using the second Excel instance?

Regards,
Brian
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

Brian,

Thanks for all your help! It works like a charm now. It was because the imported file was protected. I understand how the import file was functioning in the background. I was just trying to think of everything that could be giving me problems. Thank you again!

Very Respectfully,

Marc
Avatar of redmondb
redmondb
Flag of Afghanistan image
Marc,

Glad you're sorted! Are we finished then? (Although I'd love to know the reason for the second Excel instance!)

Regards,
Brian.
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

Well, I am kind of new to the Excel VBA. I'm stationed overseas and kind of got thrown into this position because I had some visual basic experience in high school. Is there an easier way to pull the information rather than opening the other file?
ASKER CERTIFIED SOLUTION
Avatar of redmondb
redmondb
Flag of Afghanistan image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of redmondb
redmondb
Flag of Afghanistan image
Marc,

As a complete aside, there's another Question open at the moment about a problem running Excel as another instance. What's apparently happening is that the other Excel doesn't close down when it's supposed to.

As far as I know, the only problem that this causes is that more resources (particularly memory) are used than is necessary.

It's unlikely that this should be a big concern for you, but if you want to be sure you could try the following...
Go into the Process tab in Task Manager. Sort by Image Name. You probably have this issue if you see more than one instance of Excel (or only one while Excel is apparently not running).

The other Question is still open, so if you do have the issue, I suggest that you wait until it's closed and check its solution. If you're happy you understand it then you can implement it. Alternatively, raise a new Question referring to that.

Regards,
Brian.
Avatar of AFGPHXExcel
AFGPHXExcel

ASKER

Thanks Brian. I really appreciate it. The only time I really ran into a problem with the other instance was when the code was not working correctly and I would have to go in to the Process tab and shut it down there.

I implemented your suggestion and it worked great!
Avatar of redmondb
redmondb
Flag of Afghanistan image
Thanks, Marc. All the best!